Access 2000 query problem - Help!

[tehswinstead]

Hi, I have a problem that I'm pulling my hair out over.

I work at a company that legally has to test the blood lead levels of
it's employees.
Each person must be tested every 6 months. I have the results in a
database as shown below.
(If it doesn't show up in a fixed width font google groups users can
click the top right link to fix it)
__________________________________________________ __________________________________
| BloodTesting.mdb
|
|
|
| ___________ ___________
___________ |
| | | | | |
| |
| | EMPLOYEES | | RESULTS | |
TESTS | |
| |___________| |___________|
|___________| |
| | | | | |
| |
| | id | | id | |
id | |
| |___________| |___________|
|___________| |
| | | | | |
| |
| | name |--------------------| emp_name | |-----|
date | |
| |___________| |___________| |
|___________| |
| | | | | |
|
| | startdate | | result | |
|
| |___________| |___________| |
|
| | | | | |
|
| | leavedate | | testdate |------------|
|
| |___________| |___________|
|
| | |
|
| | untilnext |
|
| |___________|
|
|
|

|_________________________________________________ ___________________________________|

I'm trying to make a query that will show me any employee whose last
test date is more than 6 months ago (i.e. they are overdue). I can do
this easily enough for employees with only one result - select emp_name
from results where dateadd("m", 6, testdate) < date(); - but anyone
with us for more than 6 months (or employees with high lead levels in
their blood get monitored every 3 months) will have more than one
result and I'm finding it hard to query only each person's most recent
test date.

I've tried playing around qith queries and subqueries and macros and
modules and for a while I thought I'd found the key in using DISTINCT
along with "order by testdate limit 1" but that didn't work either.

Can anybody point me in the right direction on this please?

[pietlinden]

Select EmployeeID, Max(TestDate) As LastTestDate
FROM EMPLOYEE
ORDER EMPLOYEE.EmployeeID
GROUP BY EmployeeID

Then you can get all their data using that and the original table.

[tehswinstead]

Not at work now so I can't test it but my first thought is, wouldn't
that give me the most revent test date in the table rather than each
employees most recent one?

[pietlinden]

nope. That's what grouping by the EmployeeID does.

[tehswinstead]

pi********@hotmail.com wrote:

nope. That's what grouping by the EmployeeID does.

Great stuff. I look forward to trying it on Monday, thanks for your
help!