Last Date for Each Group

[Guest]

I have a table with an employee id and check date. The table is a SQL Server
2000 table.

I'd like to return the last check date for each employee through a
user-defined range.

For example, if the table contains the following records:
Employee ID Check Date
ABC0001 1/31/2006
ABC0001 2/28/2006
ABC0001 3/31/2006
DEF0002 1/31/2006
DEF0002 3/31/2006

I'd like to enter a date, like 2/28/2006, and have the query return the
following records:
ABC0001 2/28/2006
DEF0002 1/31/2006

I have a query with Group By for Employee ID and Max for the date. The query
Access is generating is:
SELECT tblTest.EmployeeID, Max(tblTest.CheckDate) AS MaxOfCheckDate
FROM tblTest
GROUP BY tblTest.EmployeeID
HAVING (((Max(tblTest.CheckDate))<=#2/28/2006#));

The results are no records.

What am I doing wrong?

Thank you.

 

[Guest]

Okay, I figured out a way to do it. I created a query that has the date
restriction and then a query based on the first query that does the max. I'm
sure there's some great SQL code that could be written to do it all in one
query and if anybody knows it, that would be very appreciated.

Thank you.

 

[John Spencer]

Use a WHERE clause instead of a HAVING clause.
The Where clause will limit the records returned before the Max is
calculated.
Having limits the records after the calculation takes place.
To get the maximum date that is on or before Feb 28 2006 you would have a
query that looks like

SELECT tblTest.EmployeeID, Max(tblTest.CheckDate) AS MaxOfCheckDate
FROM tblTest
WHERE tblTest.CheckDate <=#2/28/2006#
GROUP BY tblTest.EmployeeID

 

[Crystal]

Thanks, John

that makes a lot of sense!

Have an awesome day

Warm Regards,
Crystal