Most Recent Date Query

[Tom Alway]

Okay, I'm going to try this again. I posted a question
last week and got a correct answer for the information
that I requested. I guess it would have helped if I gave
the correct information. Anyway, here is my problem
(again). I have a table that contains the following
information:

Emp_Name Number Start_date Dept
Adams, Bob 51 7/1/2000 Training
Adams, Bob 51 7/31/2004 Games
Smith, Jim 75 1/1/2000 Food
Smith, Jim 75 1/1/2004 Shipping
Johns, Mark 23 6/3/2000 Shipping
Johns, Mark 23 6/3/2004 Maint
Hansen, Sue 12 1/1/2000 Admin
Hansen, Sue 12 1/5/2004 Games
Adams, Mary 44 7/1/1999 Training
Adams, Mary 44 8/7/2004 Food

I wish to query the data and return the most recent start
date that a particular person had and the department
where they started. Therefore my results would look like:

Name Number Startdate Dept
Adams, Bob 51 7/31/2004 Games
Smith, Jim 75 1/1/2004 Shipping
Johns, Mark 23 6/3/2004 Maint
Hansen, Sue 12 1/5/2004 Games
Adams, Mary 44 8/7/2004 Food

As suggested from my earily post, I have tried using

SELECT Name, MAX([Startdate]) AS MostRecentDate
FROM MyTable
GROUP BY Name;

and can get it to work for two columns (name and
startdate) but when I add all of the columns I end up
with invalid data. Any help would be greatly appreciated.

Thank you.

 

[Tom Ellison]

Dear Tom:

You've moved out of the realm of what the totals query can do. While
you can get the totals query to work for Emp_Name and Number, it won't
do the Dept column the way you want.

To illustrate, you can extend the totals query like this, omitting for
the moment the Dept column:

SELECT Emp_Name, Number, MAX(Start_date) AS MostRecentDate
FROM MyTable
GROUP BY Emp_Name, Number

However, finding which Departments are involved in rows that have that
MostRecentDate (and likely there could be more than one, unless you
have made Emp_Name/Number/Start_date to be a unique index) is a common
query problem that even has a name: a correlated subquery.

In your case, it is constructed like this:

SELECT Emp_Name, Number, Start_date as MostRecentDate, Dept
FROM MyTable T
WHERE Start_date = (SELECT MAX(Start_date) FROM MyTable T1
WHERE T1.Emp_Name = T.Emp_Name AND T1.Number = T.Number)

In your sample data you have no instances where an Emp_Name has two
different numbers. But perhaps such a thing is possible. I suggest
that for testing, you may want to create some data like this. Another
thing you don't have in the test data is an instance where the same
Emp_Name and Number have two entries for the same Start_date. If this
is possible, even by mistake, then your query needs to account for
that. For testing, and to illustrate what can happen, I recommend you
try this. Also, if you want to exclude this from every happening,
create a unique index on Emp_name, Number, and Start_date.

No matter what circumstances you run into, the above query will serve
well, but it is best to be prepared for all the ways it will react to
situations you may encounter in the future.

Finally, it may be instructive to see why the simple totals query will
fail with the inclusion of Dept:

SELECT Emp_Name, Number, MAX(Start_date) AS MostRecentDate, Dept
FROM MyTable
GROUP BY Emp_Name, Number, Dept

This will give entirely different results. Indeed, for the sample
data it will simply return every row.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Okay, I'm going to try this again. I posted a question
last week and got a correct answer for the information
that I requested. I guess it would have helped if I gave
the correct information. Anyway, here is my problem
(again). I have a table that contains the following
information:

Emp_Name Number Start_date Dept
Adams, Bob 51 7/1/2000 Training
Adams, Bob 51 7/31/2004 Games
Smith, Jim 75 1/1/2000 Food
Smith, Jim 75 1/1/2004 Shipping
Johns, Mark 23 6/3/2000 Shipping
Johns, Mark 23 6/3/2004 Maint
Hansen, Sue 12 1/1/2000 Admin
Hansen, Sue 12 1/5/2004 Games
Adams, Mary 44 7/1/1999 Training
Adams, Mary 44 8/7/2004 Food

I wish to query the data and return the most recent start
date that a particular person had and the department
where they started. Therefore my results would look like:

Name Number Startdate Dept
Adams, Bob 51 7/31/2004 Games
Smith, Jim 75 1/1/2004 Shipping
Johns, Mark 23 6/3/2004 Maint
Hansen, Sue 12 1/5/2004 Games
Adams, Mary 44 8/7/2004 Food

As suggested from my earily post, I have tried using

SELECT Name, MAX([Startdate]) AS MostRecentDate
FROM MyTable
GROUP BY Name;

and can get it to work for two columns (name and
startdate) but when I add all of the columns I end up
with invalid data. Any help would be greatly appreciated.

Thank you.

 

[John Vinson]

I wish to query the data and return the most recent start
date that a particular person had and the department
where they started.

A Subquery will be needed to do this (since you want to return all of
the fields in the most recent record, rather than just finding the
most recent date regardless of which record it's in).

Create a Query based on your table; on the Criteria line under the
Start_Date field put

(SELECT Max([Start_Date]) FROM yourtable AS X
WHERE X.[Number] = yourtable.[Number])

to find the maximum (i.e. most recent) date for each value of Number.

 

[LeAnne]

Hi Tom,

Sigh. There's a lovely, elegant way to do this using ONE query w/a
subquery, but I've always found it easier just to create 2 queries:

QUERY 1: SELECT [Emp_Number], MAX([Start_date]) AS MostRecentDate
FROM YourTable
GROUP BY Emp_Number;

QUERY 2: SELECT YourTable.Emp_Name, YourTable.Emp_Number,
YourTable.Start_date, YourTable.Dept
FROM YourTable INNER JOIN QUERY1 ON (YourTable.Start_date =
QUERY1.MostRecentDate) AND (YourTable.Emp_Number = QUERY1.Emp_Number);

Note that I used employee number instead of the Emp_Name field, since in
your example data the info in that field is not atomic (not always
necessary, IMO, but db convention usually splits people's names into 2-3
fields (e.g. FirstName, LastName, MiddleInit)). Also note that I changed
the fieldname from Number to Emp_Number, since "Number" is a reserved
word (that is, it refers to a specific function or property) in Access,
and could therefore cause you problems later on.

hth,

LeAnne