Max Query

  • Thread starter Thread starter TitaniaTiO2
  • Start date Start date
T

TitaniaTiO2

I am trying to use the following Query

SELECT tblTraining.PersonID, tblTraining.DocumentNumber,
tblDocument.DocumentTitle, tblTraining.EmployeeDocumentStatus,
tblTraining.TrainingStatus, Max(tblTraining.HistoryTrainedTo) AS
MaxHistoryTrainedTo, tblDocument.DocumentLifeCycle
FROM tblDocument INNER JOIN tblTraining ON tblDocument.DocumentNumber =
tblTraining.DocumentNumber
GROUP BY tblTraining.PersonID, tblTraining.DocumentNumber,
tblDocument.DocumentTitle, tblTraining.EmployeeDocumentStatus,
tblTraining.TrainingStatus, tblDocument.DocumentLifeCycle
HAVING
(((tblTraining.PersonID)=[Forms]![frmEditEmployeeInformation]![txtPersonID])
AND ((tblDocument.DocumentLifeCycle)="Active"));


I want the query to display ONLY the Max HistoryTrainedTo Number for each
document and allow me to edit some fields.

Right now the query displays all histories trained to and will not allow me
to edit any fields.

Any suggestions on how I can proceed?

Thanks

Titania
 
As the old saying goes, 'You can not have your cake and eat it too.'
You need to use a totals query to pull the tblTraining.DocumentNumber and
Max(tblTraining.HistoryTrainedTo) AS MaxHistoryTrainedTo only.
Then join that query in a select (not totals) on the
tblTraining.DocumentNumber.
 
Back
Top