Get Most Recent Date In Query

[ccjjscb]

Hello and thanks for any help,
I have a cross tab query that has training courses as the row header, employees as the column header, and the training dates as the fields. I want to be able to get the most recent training dates drawn into the query. Is there an expression that would give me the most recent date (not the most recent entry)for each feild? The database was constructed from old data and the dates were not enter in any kind of order, so the most recent entry may not be the most recent training date.
Thanks again for any help
Chris

 

[lespaul]

What's the existing SQL?

Leslie

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

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[ccjjscb]

Here is the SQL for the query:

TRANSFORM Var(qryTrainingDates.[Training Date]) AS [VarOfTraining Date]
SELECT qryTrainingDates.[Course Name]
FROM qryTrainingDates
GROUP BY qryTrainingDates.[Course Name]
PIVOT qryTrainingDates.[Employee Full Name];

Hope this is what you were asking for.
Thanks
Chris

 

[PHV]

Have you tried to use Max instead of Var ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ccjjscb]

Thanks PHV,
Tried your suggestion and ran the query and it appears that it did the trick. Thanks again for the great help and thanks to lespaul for asking for the right info.
Chris