Inner Join within one table?

[Guest]

I am wondering if you can run an inner join just using one table. I can
explain why I would want to do this and perhaps there could be a simpler way.

I have four fields in my table (TrackID, ShipID, Date, Status)

I need to query the earliest Date and obtain the ShipID. There will always
be a TrackID, however, if the Status is a specific value (38), there will be
no ShipID, but I still need that date. If a Status of 38 were to exist, it
would always be the earliest date. If the Status is anything else, there will
be a ShipID and no problem.

So what I wanted to do is query the TrackID and Date and do an inner join
with TrackID and ShipID. Each TrackID will have an unique ShipID regardless
of how many different status codes there are which would produce duplicates.


Is this achieveable or even an easier way to do this?

Any input will be greatly appreciated. Thanks ahead of time!

 

[Guest]

There are multiple TrackIDs and I need the earliest date for each one. Sorry
for the confusion

 

[Cindy]

What is your primary key?
Are you looking for the earliest date for the same track ID?

 

[Guest]

There is no Primary Key because based on the status code changing the TrackID
could be populated in multiple rows. However, the TrackID is the unique
identifier.
And yes, I am looking for the earliest date per TrackID.

I actually just figured it out. I used

SELECT [Customer].[TrackID], MAX([Customer].[ShipID]) AS ["ShipID"],
MIN([Customer].[Date]) AS ["Date"]

This selected the ShipID since it is either 0 when blank or the actual
ShipID when populated, and it also selects the earliest date.