Query by max date

[jminn0311]

I need help creating a query that will return multiple records with a maximum/last date.

There are two fields in the table

F1=Category F2=Date
Pumps
Motors
Instrumentation

The category has about 9 different selections you can make.

I need the query to return the latest record from each category in that field.

Thanks for your help.

Jeff M.

 

[lupins46]

If the table is literally 2 fields then you can create a simple Group query to group by Category and select the MAX date.

 

[scriverb]

Give this SQL a try:

Select A.Category, Max(A.Date) as MaxDate 
FROM [i][red]yourtablename[/red][/i] as A 
GROUP BY A.Category 
ORDER BY A.Category;

Post back with the results.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[jminn0311]

Thank you Bob,
It worked like a charm.

Jeff M.

 

[jminn0311]

How can I get this work if I want to add other fields to the query. IE CompletedBy and AuditDate. When I add these to the select query it automatically puts them in the group by selection and I can't figure out a way around it.

Jeff M

 

[scriverb]

Name and Save the above SQL. Now update the blue queryname in the below SQL to get your desired results:

Select A.*, B.MaxDate 
FROM [i][red]yourtablename[/red][/i] as A INNER JOIN [i][blue]yourQUERYname[/blue][/i] as B ON (A.Category & A.Date) = (B.Category & B.Date) 
ORDER BY A.Category;

Post back with the results.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]