Help with DISTINCT

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
Thanks Jerry, this worked great!

Jerry Whittle said:
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.


RWhittet said:
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
Click to expand...
Click to expand...
 
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;
 
Back
Top