Query by name AND last record

[Minkey]

I have the following columns in a table:

ID Room Hours used
1 1A 100
2 1A 200
3 2A 50
4 2A 55

and I need to ultimately show the last record of each type of room:

ID Room Hours used
2 1A 200
4 2A 55

Can anyone suggest the best way of achieving this - I don't want to use a separate table/query for each room as there will be hundreds of them !

I've used a combo box to filter the record and display the last one which is fine to view but this info has to be put into a report without any user intervention hence the query.

Any help/ suggestions would be greatly appreciated

 

[dcx693]

Yes, it's possible, but do you really need to repeat the ID field in the final results? If not, you save yourself a step with this:

SELECT tblRoomUsage.Room, Last(tblUsage.Hours) AS LastOfHours
FROM tblUsage
GROUP BY tblRoomUsage.Room;

if you need the ID number, just join that query back to the original table to get the ID number.

 

[raskew]

This would retain the ID--

SELECT
    tblRoomUsage.Room
  , Last(tblRoomUsage.ID) AS LastOfID
  , Last(tblRoomUsage.HoursUsed) AS LastOfHoursUsed
FROM
   tblRoomUsage
GROUP BY
   tblRoomUsage.Room;

Bob

 

[Minkey]

dcx693 - thanks but yes I do need to use ID

raskew - Fantastic, just what I needed

Thanks to you both.

This forum is great I've used access for a while to do simple stuff but now I'm using it in anger (so to speak) this place has been invaluable.

 

[Minkey]

Thanks Pat,

I do have a large amount of data available which I will eventually test with so thanks for the heads up