Problem retrieving the MAX date in a a query

[Jason M]

I am having a bit of trouble recieving a max date that an event was entered
into a data baase. It only crops up for 4 records out of about 650 and for
those 4 I get duplicate max records (with entirely different dates!).
I am not sure where to proceed from this point. The SQL is posted below, and
if anyone here can help I would be very appreciative.
Thanks in advance,
Jason

SELECT [firstName] & " " & [LastName] AS EmployeeName,
Departments.Department, tblEmployees.Status, tblTrainingTypes.Training,
tblTrainingTypes.Frequency, Max(tblTraining.Date) AS MaxOfDate,
[Date]+[Frequency] AS DueDate, tblTrainingTypes.Initial,
tblTrainingTypes.Annual, tblTrainingTypes.Changes,
tblTrainingTypes.BiAnnually, tblTrainingTypes.Voluntary,
tblTraining.TrainingLocation, tblTraining.Instructor, tblTraining.Notes
FROM tblTrainingTypes INNER JOIN ((Departments INNER JOIN tblEmployees ON
Departments.Department = tblEmployees.Department) INNER JOIN tblTraining ON
tblEmployees.employeeID = tblTraining.EmployeeID) ON
tblTrainingTypes.Training = tblTraining.Training
GROUP BY [firstName] & " " & [LastName], Departments.Department,
tblEmployees.Status, tblTrainingTypes.Training, tblTrainingTypes.Frequency,
[Date]+[Frequency], tblTrainingTypes.Initial, tblTrainingTypes.Annual,
tblTrainingTypes.Changes, tblTrainingTypes.BiAnnually,
tblTrainingTypes.Voluntary, tblTraining.TrainingLocation,
tblTraining.Instructor, tblTraining.Notes
HAVING (((tblEmployees.Status)="Active"));

 

[Peter Hoyle]

I would suspect that you have some data entry problems.

If any of the fields that you are grouping by is the slightest bit different
then you will
get duplicates of MaxOfDate for a given person's training.

There is plenty of scope in the GROUP BY fields: -
[firstName] & " " & [LastName], Departments.Department,
tblEmployees.Status, tblTrainingTypes.Training, tblTrainingTypes.Frequency,
[Date]+[Frequency], tblTrainingTypes.Initial, tblTrainingTypes.Annual,
tblTrainingTypes.Changes, tblTrainingTypes.BiAnnually,
tblTrainingTypes.Voluntary, tblTraining.TrainingLocation,
tblTraining.Instructor, tblTraining.Notes

Any slight variation in one of these fields will give another MaxOfDate.

You could do with extracting the records that are in error and working
back to where the data entry has gone wrong.

Cheers,
Peter

 

[Jason M Canady]

Thanks very much for your input, and you weren't too far off. There were a
few data errors and I believe that that was what was causing the mistake. I
ended up deleting some records, cleaning up the rest and that seemed to fix
everything.
Thanks again for helping.
Jason