Is there a way to simplify this?

[Bogzla]

So I have an access application to keep track of the status of small programmable PCBs that we use..
every time one of these pcbs is relocated or reprogrammed, an entry is made into a table in my db (tblLocation) with a unique ID for that action, the ID of the PCB (SD), the location it is going to, the new owner, the firmware version it's been programmed with and the date. Hence, multiple (dated) entries for each board are possible.

what I need to try and do is retrieve the most recent set of data from this table for every board we own... so far I have come up with a method that uses 2 queries, but I would like to combine this into one SQL statement that I can pass to the access db from an excel front end using ADO...

here's my 2 queries:
#1 (qryLastEntry) finds the date of the the most recent entry for each board:

SELECT DISTINCT tblLocation.SD, Max(tblLocation.Date) AS LatestDate
FROM tblLocation
GROUP BY tblLocation.SD;

#2 uses the result set from the above to retrieve only the most recent set of data for each board:

SELECT tblLocation.SD, tblLocation.Date, tblLocation.Owner, tblLocation.Location, tblLocation.Firmware
FROM qryLastEntry INNER JOIN tblLocation ON (qryLastEntry.LatestDate = tblLocation.Date) AND (qryLastEntry.SD = tblLocation.SD)
ORDER BY tblLocation.SD;

If there is a way to combine the 2 into one statement, that would be very useful, as although this method works just fine with access and using the built-in 'get external data' feature within excel, I'd like to be able to manipulate the SQL pragramatically (and also, I may want to migrate to mysql so a cross-platform solution would definitely be preferable in the long run).
I've had a look at using subqueries to achieve this but with no luck so far..

thanks in advance,
Bogzla

 

[Dennisk]

I've been using the 2 table method ever since Access came out in the early 90s