Filter duplicate Query Results

[hotrod1952 via AccessMonster.com]

SQL is:
SELECT WO.WOID, WOLabor.WOLaborDate, WO.DateRequired, EMPLOYEE.Name
FROM (WO INNER JOIN WOLabor ON WO.WONo = WOLabor.WONo) INNER JOIN EMPLOYEE ON
WO.AssignedToEmployee = EMPLOYEE.ID
GROUP BY WO.WOID, WOLabor.WOLaborDate, WO.DateRequired, EMPLOYEE.Name, WO.
DateCreated
HAVING (((WO.WOID) Like "PM*") AND ((WO.DateCreated)>=[START DATE dd/mm/yy]
And (WO.DateCreated)<DateAdd("d",1,[END DATE dd/mm/yy])) AND (([WOLabor]!
[WOLaborDate])>[WO]![DateRequired]))
ORDER BY WO.WOID, WO.DateCreated;

I have duplicate WOLaborDate entries in my table so my query returns the
duplicates. I want only the last enrty for each WO.WOID.

 

[Guest]

At first I thought that this was going to be easy; however, if your
WOLaborDate is duplicated, how do you tell which one is the last?
WO.DateCreated? If so try this:

SELECT WO.WOID,
WOLabor.WOLaborDate,
WO.DateRequired,
EMPLOYEE.Name,
Max(WO.DateCreated)
FROM (WO INNER JOIN WOLabor ON WO.WONo = WOLabor.WONo) INNER JOIN EMPLOYEE
ONWO.AssignedToEmployee = EMPLOYEE.ID
GROUP BY WO.WOID,
WOLabor.WOLaborDate,
WO.DateRequired,
EMPLOYEE.Name
HAVING (((WO.WOID) Like "PM*")
and ((WO.DateCreated)>=[START DATE dd/mm/yy]
and (WO.DateCreated)<DateAdd("d", 1, [END DATE dd/mm/yy]))
and (([WOLabor]![WOLaborDate])>[WO]![DateRequired]))
ORDER BY WO.WOID

You may need to mess with the Order By clause.

 

[hotrod1952 via AccessMonster.com]

There is a field in the WOLabor table called WOLaborNo which is an autonumber
field for each entry. If I can only figure a way to pick the largest of the
all for each duplicate in that field. HMM...

At first I thought that this was going to be easy; however, if your
WOLaborDate is duplicated, how do you tell which one is the last?
WO.DateCreated? If so try this:

SELECT WO.WOID,
WOLabor.WOLaborDate,
WO.DateRequired,
EMPLOYEE.Name,
Max(WO.DateCreated)
FROM (WO INNER JOIN WOLabor ON WO.WONo = WOLabor.WONo) INNER JOIN EMPLOYEE
ONWO.AssignedToEmployee = EMPLOYEE.ID
GROUP BY WO.WOID,
WOLabor.WOLaborDate,
WO.DateRequired,
EMPLOYEE.Name
HAVING (((WO.WOID) Like "PM*")
and ((WO.DateCreated)>=[START DATE dd/mm/yy]
and (WO.DateCreated)<DateAdd("d", 1, [END DATE dd/mm/yy]))
and (([WOLabor]![WOLaborDate])>[WO]![DateRequired]))
ORDER BY WO.WOID

You may need to mess with the Order By clause.

SQL is:
SELECT WO.WOID, WOLabor.WOLaborDate, WO.DateRequired, EMPLOYEE.Name

[quoted text clipped - 9 lines]

I have duplicate WOLaborDate entries in my table so my query returns the
duplicates. I want only the last enrty for each WO.WOID.

 

[Guest]

That could work; however, autonumbers don't always increment upward. The only
thing an autonumber is garenteed to be is unique if using them as a primary
key.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

There is a field in the WOLabor table called WOLaborNo which is an autonumber
field for each entry. If I can only figure a way to pick the largest of the
all for each duplicate in that field. HMM...

At first I thought that this was going to be easy; however, if your
WOLaborDate is duplicated, how do you tell which one is the last?
WO.DateCreated? If so try this:

SELECT WO.WOID,
WOLabor.WOLaborDate,
WO.DateRequired,
EMPLOYEE.Name,
Max(WO.DateCreated)
FROM (WO INNER JOIN WOLabor ON WO.WONo = WOLabor.WONo) INNER JOIN EMPLOYEE
ONWO.AssignedToEmployee = EMPLOYEE.ID
GROUP BY WO.WOID,
WOLabor.WOLaborDate,
WO.DateRequired,
EMPLOYEE.Name
HAVING (((WO.WOID) Like "PM*")
and ((WO.DateCreated)>=[START DATE dd/mm/yy]
and (WO.DateCreated)<DateAdd("d", 1, [END DATE dd/mm/yy]))
and (([WOLabor]![WOLaborDate])>[WO]![DateRequired]))
ORDER BY WO.WOID

You may need to mess with the Order By clause.

SQL is:
SELECT WO.WOID, WOLabor.WOLaborDate, WO.DateRequired, EMPLOYEE.Name

[quoted text clipped - 9 lines]

I have duplicate WOLaborDate entries in my table so my query returns the
duplicates. I want only the last enrty for each WO.WOID.