Using MAX() or LAST() to get records most recent date

[degraves]

Table: SAP DED DUMP
Query: qrySAP DED DUMP

Original query SQL:

SELECT
[SAP DED DUMP].PayArea,
[SAP DED DUMP].PersonnelIDNumber,
[SAP DED DUMP].FullName,
[SAP DED DUMP].[Start Date],
[SAP DED DUMP].[End Date],
[SAP DED DUMP].Amount,
[SAP DED DUMP].PUID

FROM [SAP DED DUMP]

WHERE ((([SAP DED DUMP].PersonnelIDNumber)=nnnnnn))

ORDER BY [SAP DED DUMP].FullName;

Ok using a specific personnel number, I know there are two records for this query (using a particular person's id# in the place of nnnnnn as an example).

What happens is an employee may change pay grades at any point in time and thus their deduction amounts will change. When this happens a new record is created for them reflecting the amount change. Along with this, their old record's end date is changed to the date of the change in pay and the new record is given the end date of the fiscal, annual, or academic year depending on their new assignment.

After doing some research, I understand that in order to return only the record with latest date I would need to use either Max or Last in this form

(Select (Max([End Date])) from [SAP DED DUMP])

Barring me setting this up wrong and getting the aggregate error, I end up with no records at all. The end dates may be soemthing like 10/15/2009 and 06/15/2010. I would expect to get the 2010 date as a result, but instead I get nothing back.

query that returns no records:

SELECT
[SAP DED DUMP].[Start Date],
Max([SAP DED DUMP].[End Date]) AS [MaxOfEnd Date],
[SAP DED DUMP].Amount,
[SAP DED DUMP].PUID,
[SAP DED DUMP].PayArea,
[SAP DED DUMP].PersonnelIDNumber,
[SAP DED DUMP].FullName

FROM [SAP DED DUMP]

GROUP BY [SAP DED DUMP].[Start Date],
[SAP DED DUMP].Amount,
[SAP DED DUMP].PUID,
[SAP DED DUMP].PayArea,
[SAP DED DUMP].PersonnelIDNumber,
[SAP DED DUMP].FullName

HAVING

(((Max([SAP DED DUMP].[End Date]))=(Select (Max([End Date])) from [SAP DED DUMP]))

AND

(([SAP DED DUMP].PersonnelIDNumber)=nnnnnn))
ORDER BY [SAP DED DUMP].FullName;

So...what am I missing?

Durand

 

[pbaldy]

One way:

http://www.baldyweb.com/LastValue.htm

 

[MarkK]

You are comparing the Max() in the main query to the Max() in the subquery, but this will only be true for the record that is the Max() so the query doesn't return records.
Don't do both Max() operations (and you don't need the table name prefix when there's only one table)....

SELECT [Start Date], Amount, PUID, PayArea, PersonnelIDNumber, FullName
FROM [SAP DED DUMP]
WHERE [End Date] = ( Select Max([End Date]) from [SAP DED DUMP] )
AND PersonnelIDNumber = nnnnnn
ORDER BY [SAP DED DUMP].FullName

This query too, will not return records if the max EndDate in the table doesn't belong to the Personnel 'nnnnnn'. The subquery might need to be....

SELECT Max([End Date]) from [SAP DED DUMP] WHERE PersonnelID = nnnnnn

Cheers,

 

[degraves]

One way:

http://www.baldyweb.com/LastValue.htm

Worked perfectly, thank you very much

 

[pbaldy]

No problemo, and welcome to the site by the way!

 

[degraves]

See, I tried it that way too. It makes logical sense, but instead of giving me the most recent datefield.record, it instead filters out all of the entries that have the same ID but different dates.

i.e. from the original table
uid|name|personnel#|end date
1|John Smith|1234|10/12/2009
2|Susan Jones|4321|06/15/2010
3|John Smith|1234|06/15/2010

run through the query would only yield the record for Susan Jones.

It's really odd.

At any rate, the solution baldy suggested worked perfectly. I appreciate your answer, at least I know I'm not going crazy in that line of thinking.

+rep

You are comparing the Max() in the main query to the Max() in the subquery, but this will only be true for the record that is the Max() so the query doesn't return records.
Don't do both Max() operations (and you don't need the table name prefix when there's only one table)....

SELECT [Start Date], Amount, PUID, PayArea, PersonnelIDNumber, FullName
FROM [SAP DED DUMP]
WHERE [End Date] = ( Select Max([End Date]) from [SAP DED DUMP] )
AND PersonnelIDNumber = nnnnnn
ORDER BY [SAP DED DUMP].FullName

This query too, will not return records if the max EndDate in the table doesn't belong to the Personnel 'nnnnnn'. The subquery might need to be....

SELECT Max([End Date]) from [SAP DED DUMP] WHERE PersonnelID = nnnnnn

Cheers,

 

[degraves]

Thank you very much. I think I'll hang around for a while