Re: Query to find most recent date

Tech-Archive recommends: Speed Up your PC by fixing your registry

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.
--
Dave Hargis, Microsoft Access MVP


"LadyIlsebet" wrote:

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

On May 22, 1:38 pm, Klatuu <Kla...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
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?
--
Dave Hargis, Microsoft Access MVP

"LadyIlsebet" wrote:
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


.

Relevant Pages

  • Re: Comparing the Results from two queries
    ... you must substitute the actual names of your queries for Q1 ... I have assumed that employeeID in query 2 would never be NULL. ... > 2) - Training completed by Employee ... > multiple training modules, even the same modules across multiple years, ...
    (microsoft.public.access.queries)
  • RE: Display query data based on another query
    ... You have to build a query with all the information, badge numbers, employee ... I linked BadgeNr with BadgeNr and I am showing no results. ...
    (microsoft.public.access.queries)
  • Re: Stupid Query Question...Column Display
    ... I could give a better answer if you can post examples of a couple of records from each of the Tables used in your Queries, plus the SQL of any Queries you have defined, plus an illustration of what you'd like your Query to generate from your example records. ... I was however getting the results that I need, but I'm still getting all of the empty "table column headings" with blank cells? ... It's a simple query that contains the [Employee Name],, and about 15 other columns of named job's. ...
    (microsoft.public.access.queries)
  • Re: Comparing the Results from two queries
    ... Employee by Comparison] ON ([Training Completed By Employee by ... you must substitute the actual names of your queries for Q1 ... > I have assumed that employeeID in query 2 would never be NULL. ... > "Mel Morris " ...
    (microsoft.public.access.queries)
  • Re: Joining two queries
    ... Break the query or queries into multiplequeries. ... tblTrainingList (this one just has a list of all trainings that need to be ... expire within a date range, or training by employee, by supervisor, etc. ...
    (microsoft.public.access.queries)