Help with DISTINCT

[Guest]

Hi, I am trying to create a query to show the last time a motor was greased.
Since we are working with several different motors, I need the Equipment ID
field to be distinct so that the query doesn't show the previous times it was
greased, but only the last time it was greased. Any help is greatly
appreciated.

This is what my query looks like:

SELECT [Work Records].Date, [Work Records].[Equipment ID], [Work
Records].[Work Description]
FROM [Equipment List] INNER JOIN [Work Records] ON [Equipment
List].[Equipment ID] = [Work Records].[Equipment ID]
WHERE ((([Work Records].[Work Description]) Like "*greased motor*"))
ORDER BY [Work Records].Date DESC
WITH OWNERACCESS OPTION;

Thanks,

Rick

 

[Guest]

SELECT Max([Work Records].Date),
[Work Records].[Equipment ID],
[Work Records].[Work Description]
FROM [Equipment List] INNER JOIN [Work Records]
ON [Equipment List].[Equipment ID] = [Work Records].[Equipment ID]
WHERE ([Work Records].[Work Description]) Like "*greased motor*"
GROUP BY [Work Records].[Equipment ID],
[Work Records].[Work Description]
ORDER BY Max([Work Records].Date) DESC ;

This will only work if [Work Records].Date is an actual Date/Time field.

 

[Guest]

Thanks Jerry, this worked great!

SELECT Max([Work Records].Date),
[Work Records].[Equipment ID],
[Work Records].[Work Description]
FROM [Equipment List] INNER JOIN [Work Records]
ON [Equipment List].[Equipment ID] = [Work Records].[Equipment ID]
WHERE ([Work Records].[Work Description]) Like "*greased motor*"
GROUP BY [Work Records].[Equipment ID],
[Work Records].[Work Description]
ORDER BY Max([Work Records].Date) DESC ;

This will only work if [Work Records].Date is an actual Date/Time field.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi, I am trying to create a query to show the last time a motor was greased.
Since we are working with several different motors, I need the Equipment ID
field to be distinct so that the query doesn't show the previous times it was
greased, but only the last time it was greased. Any help is greatly
appreciated.

This is what my query looks like:

SELECT [Work Records].Date, [Work Records].[Equipment ID], [Work
Records].[Work Description]
FROM [Equipment List] INNER JOIN [Work Records] ON [Equipment
List].[Equipment ID] = [Work Records].[Equipment ID]
WHERE ((([Work Records].[Work Description]) Like "*greased motor*"))
ORDER BY [Work Records].Date DESC
WITH OWNERACCESS OPTION;

Thanks,

Rick

 

[John Spencer]

SELECT [Work Records].Date, [Work Records].[Equipment ID],
[Work Records].[Work Description]
FROM [Equipment List] INNER JOIN [Work Records] ON
[Equipment List].[Equipment ID] = [Work Records].[Equipment ID]
WHERE ((([Work Records].[Work Description]) Like "*greased motor*"))
AND [Date] =
(SELECT MAX(W.[Date])
FROM [Work Records] as W
WHERE W.[Equipment ID] = [Equipment List].[Equipment ID])
ORDER BY [Work Records].Date DESC
WITH OWNERACCESS OPTION;