Struggling with an Aggregate Query

[Indigo]

I have been searching for an answer and have had no luck. I am using Access 2016 and I am trying to build an aggregate query that will pull results based on two scenarios. I can get one to work, but not the other and I don't know where else to look or try. I have a table that stores audit results on job processes. The fields are as follows:

AuditID (PK)
AuditDate
JobProcess
Auditor
Score - results here could be "Good" or "No Good".

Here is a sample of some data:

1 April 13 Job 1 John Smith Good
2 April 25 Job 1 John Smith No Good
3 April 26 Job 1 Jane Doe Good
4 April 26 Job 2 Jane Doe Good

I want the query results to be as follows:

April 25 Job 1 John Smith No Good
April 26 Job 1 Jane Doe Good
April 26 Job 2 Jane Doe Good

Now, I have been able to create an aggregate query that produces the Max by Date, but then only returns one of Jane Doe's records and not both. If I try to create a query to show both of Jane Doe's records, I also get BOTH of John Smith's, and I only want the latest. This is the SQL I have been working on:

SELECT Max(Audit.AuditDate) AS AuditDate, Max(Audit.JobProcess) AS JobProcess, Audit.Auditor, Audit.Score
FROM Audit 
GROUP BY Audit.Auditor, Audit.Score
HAVING ((Max(Audit.AuditDate)) Between [Forms]![frmAudit]![FromDate] And [Forms]![frmAudit]![ToDate]);

Can anyone offer any guidance? Thank you.

 

[plog]

I don't think you want MAX(Audit.JobProcess) in the SELECT. You want Audit.JobProcess in the SELECT and the GROUP BY:

SELECT MAX(AuditDate) AS LastAudit, JobProcess, Auditor, MAX(Score) AS WorstScore
FROM Audit
GROUP BY JobProcess, Auditor

The above query will produce the results you want from the sample given. If it doesn't produce what you want for all your actual data, please post back more data where it fails.

 

[plog]

I just looked closer. My first post adheres to your sample data, but not your explanation. To do what you explained, you need a subquery:

SELECT Auditor, MAX(AuditDate) As LastAuditDate
FROM Audit
GROUP BY Auditor

That gets the latest audit date for every auditor. Name the above query 'sub1' and save it. Then, you create a new query using it and your Audit table. Link them via Auditor and AuditDate to LastAuditDate and bring in all the data you want from the Audit table. Do not aggregate that query, run it and you will have the records you want that conform to your explanation.