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.md b
|
|
|
| ___________ ___________
___________ |
| | | | | |
| |
| | 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?
Comment