Query to find most recent date

[LadyIlsebet]

I have a database with a "main" table that is linked to several
"history" tables, in one-to-many relationships. The history tables all
have an auto-number primary key, a personalID field (for employee
number), a date field, and one or two other fields (that stores
another related bit of info, like badge number, flu shot type, etc).
In the reports I'm building, I'm going to need to know the most
recently assigned badge number, date and type of most recent flu shot,
etc. I'm not sure of the best way to write these queries. Could
someone provide some guidance, please?

Thanks in advance!

Amy

 

[Klatuu]

You can do this with a totals query, but to give you the right answer, I need
to know more about your data.
What are the other one or two fields and what do they contain?

 

[LadyIlsebet]

They are text fields, or number fields, but I'm using them more as
text. I *may* just change all the fields to text because I don't have
to do any operations on the numbers.

For instance, with the flu table, I have to track the date, as well as
a 1 letter code to describe whether they got the actual shot, turned
it down, or were given clearance to not take it due to allergies. With
the badge number table, I'm just tracking the date and the badge
number (but I don't have to do any numerical operations on the badge
number). There's an employment history table that tracks date,
position type (text) and status (text). I believe those are the only
3 where I have to know the most recent information.

Amy

 

[Klatuu]

Sound like your database is pretty well designed. That makes it pretty easy.
To combine them all into one query, you will need to have four queries. You
will need a totals query for each of the three you want to find the most
recent date for some event. So, for example, for the flu shot table, you
really only need to return two fields, the employee number and the Date.
When you create the query, make it a total query and use Group By for
employee number and Max for the date.
It will return one row for each employee with the most recent (maximum) date
found.

When you get those queries defind and working, create another query and join
everything on the employee number. Now you can show all the maximums for the
various tables on one row per employee.

 

[LadyIlsebet]

That's sort of working, so far. How do I set it up so that when I get
the most recent flu shot date, I can also get the code that goes with
it? It is possible that they will have different codes for each date.
This is also definitely what I'll need to do with the employee history
table, as well, since each employment date listed will likely have a
different type and/or status as well (promotions, moving from PT to
FT, etc)

Thanks,
Amy

 

[Klatuu]

Therein lies a problem because each field in a totals query has to be
included in an aggragate function, so it would cause multiple rows to be
returned where you want only one. So what you have to do is to join the
original table back to the totals query joined on the employee and date
fields. Then it will return the code for that row.

 

[LadyIlsebet]

It must be too early for me, but I can't say I understand this. It's
been way too long since my databases course, apparently. What would
the sql or query builder look like for this?

Thanks,
Amy

 

[Klatuu]

You need two totals queries. One for the badge table. it should have only
the employee id (Group By) and the badge date (Max). The other for the flu
shot with employee id (Group By) and flu shot date (Max).

Then to pull the data together, you need a select query that starts with the
employee table. Then join the badge totals and flu shot queries on the
employeeID.
Then join the badge table on the employeeid and badge date. Then join the
flu shot table on the employee id and flu shot date. It will return the
records from the badge and flu shot tables that relate to the employee id and
max date. Then you can include whatever fields you need from those two
tables.

As to the exact SQL or query builder code, I don't know your tables, so I
can't actually post the detail.

 

[LadyIlsebet]

Ok, *now* I've got it. Higher blood sugar, caffeine and time helped
greatly, although without your explanation below, I wouldn't have
gotten it. THANKS!

Amy

 

[Klatuu]

Glad I could help.