Tricky Query

[PaulJR]

Hi, I would like to create a query in which I recall data from 2 tables in completely separate databases. Firstly, we have my database that manages the 'test status' of various units in manufacturing, all of which have unique serial numbers that go through different test stages. Every time the unit go through a test stage a new record is created to maintain history.

Then we have another database, run by a different dept, that tracks all the units and keeps a history of their 'location'. The location of the units could be Scotland, Harlow, or the US.

What I'm trying to create is a query that will output the latest 'test status' for units that are currently in Harlow.

My problem, I have tables that look like this in an extremely simplified form:

Test Status Table
Unit SerialNo TestStatus Date
A 001 1st test 11-09-08
A 001 repair 13-09-08
B 001 1st test 15-09-08
C 005 2nd test 03-09-08

Location Table
Unit SerialNo Location Date
A 001 US 01-09-08
A 001 Harlow 02-09-08
B 001 Harlow 14-09-08
B 001 US 17-09-08
C 005 Harlow 01-09-08

The query I would like to create should look like this:

Unit SerialNo TestStatus Date Location
A 001 repair 13-09-08 Harlow
C 005 2nd test 03-09-08 Harlow

Unit B is not reported by the query because it is no longer in Harlow, it was shipped to the US to the 17-09-08.

Where I'm stuck:

1) Where I'm stuck is that I don't know a method of recalling the latest record (by date), for each unit (by serial number) that are currently in Harlow. I can't simply filter on "Harlow" as this will bring up units that were previously in Harlow and have subsequently been shipped.

2) The same problem I have applies to the Test Status, I don't know how to recall the latest record only.

Any help is very much appreciated as usual

 

[neileg]

Create an aggregate query that contains the Unit SN, the date and location. Filter on Harlow and use Max on the date.

 

[PaulJR]

Its nearly working, but using the Max on the date is giving me the last date the units were in Harlow whether they have been shipped or not.

If I remove "Harlow" from the filter I get all the latest dates for all locations.

 

[PaulJR]

I think I have this under control now. I am running a query to get the Max date, unit and SN. Then I am running a new query that matches the Max Date, SN and Unit (from first query) against the original query and table. I set the location filter to "Harlow" and bingo!

As the date is formatted as "dd-mm-yy hh-nn-ss" then the date match is fool proof.

I'm no longer quite sure how I got this working, but my query results match exactly whats on the shop floor!! lol

Thanks for your help.