how to find last record in table for specified field

[installer69]

Hi,
My simple table consists of 'ID' (autonumber), 'serial' (number) and 'movement' (text)

eg:

1 1001 add to stock
2 1002 add to stock
3 1003 add to stock
4 1001 issue
5 1002 issue
6 1001 return
7 1002 installed

I need to write a query that will list the last movement of each serial. I thought I had it by setting total for 'movement' as 'last' in the query until I realised this just gives the last according to alphabetical progression!! (in the case of serial 1002 above it returned 'issue' because 'issue' comes after 'installed' alphabetically although the last movement was 'installed')

 

[hneal98]

how do we know which is the last of the group? Is it by the ID, or what?

 

[installer69]

ID SERIAL MOVEMENT
1 1001 add to stock
2 1002 add to stock
3 1003 add to stock
4 1001 issue
5 1002 issue
6 1001 return
7 1002 installed

I need to find the last 'movement' of each 'serial' ie. the last movement of serial number '1002' is 'installed' in the above table.

Thanks

 

[lespaul]

and that's because of all the serial = 1002, "installed" has the greatest ID number?

if you are using a 200x version of Access this will work:

SELECT * FROM TableName As A INNER JOIN (SELECT MAX(ID), SERIAL FROM TableName GROUP BY SERIAL) As B ON A.SERIAL = B.SERIAL AND A.ID = B.ID

replace TableName with your table's name.

if you are using an older version then we can still do this, but it needs to be set up a little differently.

Leslie