Query moves - Last location of tube

[pault]

Hi. Please, could you help me with easy query? I have database with three tables - Tubes, Moves and Locations. I would like to build query which will list all tubes with their last locations from table moves. I've attached tables and their relations.





Description of database:
I have tubes with unique ID, which can be moved from one location to another locaion several times. These moves are recorded to the table 'Moves'. Table 'Locations' is simple table with list of locations.

I would appreciate any ideas or redirection.

Regards,
Tomas

[June7]

Try TOP parameter for one approach. Review: Microsoft Access tips: Subquery basics

[pbaldy]

Does this work for you?

Most recent value

[pault]

Thank you pbaldy!
It helped me. I've built this two SQL queries:

SELECT Max(Moves.DateMove) AS MaxDate, TubeID
FROM Moves
GROUP BY Moves.TubeID;


SELECT L.LocationName, T.TubeBarcode
FROM Moves AS M, MovesMaxDates AS Q, Tubes AS T, Locations AS L
WHERE M.TubeID = Q.TubeID AND M.LocationID = L.LocationID AND T.TubeID = M.TubeID AND M.DateMove = Q.MaxDate
ORDER BY T.TubeBarcode;

Many Thanks
Tomas

[pault]

Hi June7.
That is nice approach. I cannot make functional subquery in my Access, but the SQL "SELECT TOP 1 FROM X ORDER BY X.Y DESC" works.

Thank you.
Tomas

[pbaldy]

Happy to help Tomas, and welcome to the site! I would use the JOIN rather than the WHERE clause, but that's me.