Select last record of a sequence group 1

[HitechUser]

I've searched extensively for a solution for this but can't seem to find one I can get to work. Anyway... here's a sample of my data (Using Access 2000):

PARTID User Date XXXX XXXX
CV0001 1695 xx/xx/xxxx xxxx xxxx
CV0002 1695 xx/xx/xxxx xxxx xxxx
CV0003 1695 xx/xx/xxxx xxxx xxxx
DX0001 1696 xx/xx/xxxx xxxx xxxx
DX0002 1697 xx/xx/xxxx xxxx xxxx
FX0001 1697 xx/xx/xxxx xxxx xxxx
GX0002 1697 xx/xx/xxxx xxxx xxxx
GX0003 1697 xx/xx/xxxx xxxx xxxx


I need a query to select the last PARTID for each sequence. The query needs to look at the first two characters (substring). I need this to be able to create a report so show the next available PARTID.

The query should return....

PARTID User Date XXXX XXXX
CV0003 1695 xx/xx/xxxx xxxx xxxx
DX0002 1697 xx/xx/xxxx xxxx xxxx
FX0001 1697 xx/xx/xxxx xxxx xxxx
GX0003 1697 xx/xx/xxxx xxxx xxxx


These parts will be assigned differently so I am not able to auto assign (the PARTID) them or store the next available one in a table.

Thanks in advance.

 

[PHV]

Try something like this :
SELECT A.* FROM yourTable A INNER JOIN (
SELECT Left(PARTID,2) AS Prefix, Max(PARTID) As MaxOfPartID
FROM yourTable GROUP BY Left(PARTID,2)
) M ON A.PARTID = M.MaxOfPartID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[HitechUser]

PHV

Sweeeeeeeeeeeeeet!. I am totally blown away how fast of a response I received. This was my first question on any forum. Your code was consise and quick. (Although I now need to take the time to fully understand it all.)

Sorry for the late thank you. I was tied up getting the project finished.

Thanks again.........