Query Structure Problem - Missing something obvious

[tmcfar]

Query Question:

This :
SELECT tblAttendancePoints.[EmpID], Max(tblAttendancePoints.PointsDate) AS MaxOfPointsDate, tblAttendancePoints.Total
FROM tblAttendancePoints
GROUP BY tblAttendancePoints.[EmpID], tblAttendancePoints.Total
HAVING (((tblAttendancePoints.[EmpID])=179709));

Returns:

EmpID MaxOf
Points Date Total
179709 1/30/2007 0
179709 3/20/2007 1.5
179709 6/6/2007 3
179709 6/8/2007 4.5

I want to return the last record. (6/8 - 4.5)

Seems annoyingly simple…

But when I do this:
SELECT tblAttendancePoints.[EmpID], Max(tblAttendancePoints.PointsDate) AS MaxOfPointsDate, Last(tblAttendancePoints.Total) AS LastOfTotal
FROM tblAttendancePoints
GROUP BY tblAttendancePoints.[EmpID]
HAVING (((tblAttendancePoints.[EmpID])=179709));

I get:

EmpID MaxOf
Points Date LastOf
Total
179709 6/8/2007 3

I know this has got to be really obvious…but I don't get it.

Query Properties are all default. No parameters set other than [EmpID] = 179709 to minimize the return.

The only wacky thing I can think of is that this table has a 3 field primary key on EmpID, PointsDate, CalcReason which is probably poor design.

I can (painfully) restructure the table if I know this will help.

Any suggestions?


Thanks,
Tom

 

[lespaul]

Maybe:

SELECT A.[EmpID],B.MaxOfPointsDate, A.Total
FROM tblAttendancePoints A
INNER JOIN  (SELECT tblAttendancePoints.[EmpID], Max(tblAttendancePoints.PointsDate) AS MaxOfPointsDate FROM tblAttendancePoints GROUP BY EmpID) As B ON A.[EmpID] = B.[EmpID] And A.PointsDate = B.MaxOfPointsDate
WHERE (((tblAttendancePoints.[EmpID])=179709));

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[tmcfar]

For some reason, I couldn't get Access to handle the nested query due to an error in the FROM statement.

However, when I split it out into two queries....it worked just fine.

Thanks,
Tom

 

[lespaul]

the in-line FROM statement only works in Access2000 and above. If you are using Access 97 then you have to use the two query approach.

Glad it worked for you.

Leslie

 

[PHV]

The single query way for ac97:
SELECT EmpID, PointsDate, Total
FROM tblAttendancePoints
WHERE EmpID=179709 AND PointsDate=DMax("PointsDate","tblAttendancePoints","EmpID=179709")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886