Max Query

[TitaniaTiO2]

How can I make the tblTraining.HistoryTrainedTo value be maximized in the
following query?

SELECT tblTraining.PersonID, tblTraining.DocumentNumber,
tblDocument.DocumentTitle, tblTraining.EmployeeDocumentStatus,
tblTraining.TrainingStatus, tblPerson.LastName, tblPerson.FirstName,
[LastName] & ", " & [FirstName] AS Expr1,tblTraining.HistoryTrainedTo
FROM (tblDocument INNER JOIN tblTraining ON tblDocument.DocumentNumber =
tblTraining.DocumentNumber) INNER JOIN tblPerson ON tblTraining.PersonID =
tblPerson.PersonID
GROUP BY tblTraining.PersonID, tblTraining.DocumentNumber;


Thanks

Titania

 

[Jerry Whittle]

SELECT tblTraining.PersonID,
tblTraining.DocumentNumber,
tblDocument.DocumentTitle,
tblTraining.EmployeeDocumentStatus,
tblTraining.TrainingStatus,
tblPerson.LastName,
tblPerson.FirstName,
[LastName] & ", " & [FirstName] AS Expr1,
Max(tblTraining.HistoryTrainedTo) AS MaxHistoryTrainedTo
FROM (tblDocument INNER JOIN tblTraining
ON tblDocument.DocumentNumber = tblTraining.DocumentNumber)
INNER JOIN tblPerson ON tblTraining.PersonID = tblPerson.PersonID
GROUP BY tblTraining.PersonID,
tblTraining.DocumentNumber,
tblDocument.DocumentTitle,
tblTraining.EmployeeDocumentStatus,
tblTraining.TrainingStatus,
tblPerson.LastName,
tblPerson.FirstName,
[LastName],
[FirstName] ;

There might be a little glitch in the Group By as you are returning the name
fields twice.

 

[TitaniaTiO2]

Thanks.

When I try to use this query I get the message that DocumentTitle is not
part of an aggregate function.

Thoughts?

Titania

SELECT tblTraining.PersonID,
tblTraining.DocumentNumber,
tblDocument.DocumentTitle,
tblTraining.EmployeeDocumentStatus,
tblTraining.TrainingStatus,
tblPerson.LastName,
tblPerson.FirstName,
[LastName] & ", " & [FirstName] AS Expr1,
Max(tblTraining.HistoryTrainedTo) AS MaxHistoryTrainedTo
FROM (tblDocument INNER JOIN tblTraining
ON tblDocument.DocumentNumber = tblTraining.DocumentNumber)
INNER JOIN tblPerson ON tblTraining.PersonID = tblPerson.PersonID
GROUP BY tblTraining.PersonID,
tblTraining.DocumentNumber,
tblDocument.DocumentTitle,
tblTraining.EmployeeDocumentStatus,
tblTraining.TrainingStatus,
tblPerson.LastName,
tblPerson.FirstName,
[LastName],
[FirstName] ;

There might be a little glitch in the Group By as you are returning the name
fields twice.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

How can I make the tblTraining.HistoryTrainedTo value be maximized in the
following query?

SELECT tblTraining.PersonID, tblTraining.DocumentNumber,
tblDocument.DocumentTitle, tblTraining.EmployeeDocumentStatus,
tblTraining.TrainingStatus, tblPerson.LastName, tblPerson.FirstName,
[LastName] & ", " & [FirstName] AS Expr1,tblTraining.HistoryTrainedTo
FROM (tblDocument INNER JOIN tblTraining ON tblDocument.DocumentNumber =
tblTraining.DocumentNumber) INNER JOIN tblPerson ON tblTraining.PersonID =
tblPerson.PersonID
GROUP BY tblTraining.PersonID, tblTraining.DocumentNumber;

Thanks

Titania

 

[Jerry Whittle]

My bad. Notice that I put the name fields in twice in the Group By. I was
worried that could cause problems. In the statement below I took out the
Expr1 field to simplify things. See if this works:

SELECT tblTraining.PersonID,
tblTraining.DocumentNumber,
tblDocument.DocumentTitle,
tblTraining.EmployeeDocumentStatus,
tblTraining.TrainingStatus,
tblPerson.LastName,
tblPerson.FirstName,
Max(tblTraining.HistoryTrainedTo) AS MaxHistoryTrainedTo
FROM (tblDocument INNER JOIN tblTraining
ON tblDocument.DocumentNumber = tblTraining.DocumentNumber)
INNER JOIN tblPerson ON tblTraining.PersonID = tblPerson.PersonID
GROUP BY tblTraining.PersonID,
tblTraining.DocumentNumber,
tblDocument.DocumentTitle,
tblTraining.EmployeeDocumentStatus,
tblTraining.TrainingStatus,
tblPerson.LastName,
tblPerson.FirstName;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Thanks.

When I try to use this query I get the message that DocumentTitle is not
part of an aggregate function.

Thoughts?

Titania

SELECT tblTraining.PersonID,
tblTraining.DocumentNumber,
tblDocument.DocumentTitle,
tblTraining.EmployeeDocumentStatus,
tblTraining.TrainingStatus,
tblPerson.LastName,
tblPerson.FirstName,
[LastName] & ", " & [FirstName] AS Expr1,
Max(tblTraining.HistoryTrainedTo) AS MaxHistoryTrainedTo
FROM (tblDocument INNER JOIN tblTraining
ON tblDocument.DocumentNumber = tblTraining.DocumentNumber)
INNER JOIN tblPerson ON tblTraining.PersonID = tblPerson.PersonID
GROUP BY tblTraining.PersonID,
tblTraining.DocumentNumber,
tblDocument.DocumentTitle,
tblTraining.EmployeeDocumentStatus,
tblTraining.TrainingStatus,
tblPerson.LastName,
tblPerson.FirstName,
[LastName],
[FirstName] ;

There might be a little glitch in the Group By as you are returning the name
fields twice.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

How can I make the tblTraining.HistoryTrainedTo value be maximized in the
following query?

SELECT tblTraining.PersonID, tblTraining.DocumentNumber,
tblDocument.DocumentTitle, tblTraining.EmployeeDocumentStatus,
tblTraining.TrainingStatus, tblPerson.LastName, tblPerson.FirstName,
[LastName] & ", " & [FirstName] AS Expr1,tblTraining.HistoryTrainedTo
FROM (tblDocument INNER JOIN tblTraining ON tblDocument.DocumentNumber =
tblTraining.DocumentNumber) INNER JOIN tblPerson ON tblTraining.PersonID =
tblPerson.PersonID
GROUP BY tblTraining.PersonID, tblTraining.DocumentNumber;

Thanks

Titania