Grouping query to elminate dups

  • Thread starter Thread starter gcutter
  • Start date Start date
G

gcutter

I have a table the I am pulling from like below:
ID Employee# Booth Timestamp BoothStatus
841 414 Booth05 10/22/2009 06:58:31 INBooth
844 414 Booth05 10/22/2009 07:35:37 OutofBooth
845 414 Booth06 10/22/2009 07:35:49 INBooth
846 414 Booth06 10/22/2009 09:12:49 OutofBooth
847 414 Booth09 10/22/2009 09:13:01 INBooth
854 414 Booth09 10/22/2009 10:53:03 OutofBooth
855 414 Booth05 10/22/2009 10:53:16 INBooth
860 414 Booth05 10/22/2009 12:22:43 OutofBooth
866 414 Booth05 10/22/2009 12:35:31 INBooth
893 414 Booth05 10/22/2009 15:15:07 OutofBooth

Query:
SELECT WeldBoothAssignments.ID, WeldBoothAssignments.[Employee#],
WeldBoothAssignments.Booth, WeldBoothAssignments.Timestamp AS Start,
WeldBoothAssignments_1.Timestamp AS [End]
FROM WeldBoothAssignments INNER JOIN WeldBoothAssignments AS
WeldBoothAssignments_1 ON (WeldBoothAssignments.[Employee#] =
WeldBoothAssignments_1.[Employee#]) AND (WeldBoothAssignments.Booth =
WeldBoothAssignments_1.Booth)
WHERE (((WeldBoothAssignments.Timestamp) Like "*10/22/2009*") AND
((WeldBoothAssignments.BoothStatus)="INBooth") AND
((WeldBoothAssignments_1.BoothStatus)="OutofBooth") AND
((WeldBoothAssignments_1.ID)>[WeldBoothAssignments].[ID]));

Looking to get:
ID Employee#Booth Start End
841 414 Booth05 10/22/2009 06:58:31 10/22/2009 07:35:37
845 414 Booth06 10/22/2009 07:35:49 10/22/2009 09:12:49
847 414 Booth09 10/22/2009 09:13:01 10/22/2009 10:53:03
855 414 Booth05 10/22/2009 10:53:16 10/22/2009 12:22:43
866 414 Booth05 10/22/2009 12:35:31 10/22/2009 15:15:07

What I am getting:
ID Employee#Booth Start End
841 414 Booth05 10/22/2009 06:58:31 10/22/2009 15:15:07
841 414 Booth05 10/22/2009 06:58:31 10/22/2009 12:22:43
841 414 Booth05 10/22/2009 06:58:31 10/22/2009 07:35:37
845 414 Booth06 10/22/2009 07:35:49 10/22/2009 09:12:49
847 414 Booth09 10/22/2009 09:13:01 10/22/2009 10:53:03
855 414 Booth05 10/22/2009 10:53:16 10/22/2009 15:15:07
855 414 Booth05 10/22/2009 10:53:16 10/22/2009 12:22:43
866 414 Booth05 10/22/2009 12:35:31 10/22/2009 15:15:07
Multiples of Line 841 & 855 because they clocked in & out of same booth back
to back.

Thanks in Advanced,
Greg
 
It seems that

SELECT WeldBoothAssignments.ID, WeldBoothAssignments.[Employee#],
WeldBoothAssignments.Booth, MIN(WeldBoothAssignments.Timestamp) AS Start,
MIN(WeldBoothAssignments_1.Timestamp) AS [End]
FROM WeldBoothAssignments INNER JOIN WeldBoothAssignments AS
WeldBoothAssignments_1 ON (WeldBoothAssignments.[Employee#] =
WeldBoothAssignments_1.[Employee#]) AND (WeldBoothAssignments.Booth =
WeldBoothAssignments_1.Booth)
WHERE (((WeldBoothAssignments.Timestamp) Like "*10/22/2009*") AND
((WeldBoothAssignments.BoothStatus)="INBooth") AND
((WeldBoothAssignments_1.BoothStatus)="OutofBooth") AND
((WeldBoothAssignments_1.ID)>[WeldBoothAssignments].[ID]))
GROUP BY WeldBoothAssignments.ID, WeldBoothAssignments.[Employee#],
WeldBoothAssignments.Booth

could do.


Vanderghast, Access MVP





gcutter said:
I have a table the I am pulling from like below:
ID Employee# Booth Timestamp BoothStatus
841 414 Booth05 10/22/2009 06:58:31 INBooth
844 414 Booth05 10/22/2009 07:35:37 OutofBooth
845 414 Booth06 10/22/2009 07:35:49 INBooth
846 414 Booth06 10/22/2009 09:12:49 OutofBooth
847 414 Booth09 10/22/2009 09:13:01 INBooth
854 414 Booth09 10/22/2009 10:53:03 OutofBooth
855 414 Booth05 10/22/2009 10:53:16 INBooth
860 414 Booth05 10/22/2009 12:22:43 OutofBooth
866 414 Booth05 10/22/2009 12:35:31 INBooth
893 414 Booth05 10/22/2009 15:15:07 OutofBooth

Query:
SELECT WeldBoothAssignments.ID, WeldBoothAssignments.[Employee#],
WeldBoothAssignments.Booth, WeldBoothAssignments.Timestamp AS Start,
WeldBoothAssignments_1.Timestamp AS [End]
FROM WeldBoothAssignments INNER JOIN WeldBoothAssignments AS
WeldBoothAssignments_1 ON (WeldBoothAssignments.[Employee#] =
WeldBoothAssignments_1.[Employee#]) AND (WeldBoothAssignments.Booth =
WeldBoothAssignments_1.Booth)
WHERE (((WeldBoothAssignments.Timestamp) Like "*10/22/2009*") AND
((WeldBoothAssignments.BoothStatus)="INBooth") AND
((WeldBoothAssignments_1.BoothStatus)="OutofBooth") AND
((WeldBoothAssignments_1.ID)>[WeldBoothAssignments].[ID]));

Looking to get:
ID Employee#Booth Start End
841 414 Booth05 10/22/2009 06:58:31 10/22/2009 07:35:37
845 414 Booth06 10/22/2009 07:35:49 10/22/2009 09:12:49
847 414 Booth09 10/22/2009 09:13:01 10/22/2009 10:53:03
855 414 Booth05 10/22/2009 10:53:16 10/22/2009 12:22:43
866 414 Booth05 10/22/2009 12:35:31 10/22/2009 15:15:07

What I am getting:
ID Employee#Booth Start End
841 414 Booth05 10/22/2009 06:58:31 10/22/2009 15:15:07
841 414 Booth05 10/22/2009 06:58:31 10/22/2009 12:22:43
841 414 Booth05 10/22/2009 06:58:31 10/22/2009 07:35:37
845 414 Booth06 10/22/2009 07:35:49 10/22/2009 09:12:49
847 414 Booth09 10/22/2009 09:13:01 10/22/2009 10:53:03
855 414 Booth05 10/22/2009 10:53:16 10/22/2009 15:15:07
855 414 Booth05 10/22/2009 10:53:16 10/22/2009 12:22:43
866 414 Booth05 10/22/2009 12:35:31 10/22/2009 15:15:07
Multiples of Line 841 & 855 because they clocked in & out of same booth
back
to back.

Thanks in Advanced,
Greg
Click to expand...
 
Here is the query you need:

SELECT WeldBoothAssignments.ID, WeldBoothAssignments.[Employee#],
WeldBoothAssignments.Booth, WeldBoothAssignments.Timestamp AS Start,
WeldBoothAssignments_1.Timestamp AS [End]
FROM WeldBoothAssignments INNER JOIN WeldBoothAssignments AS
WeldBoothAssignments_1 ON (WeldBoothAssignments.[Employee#] =
WeldBoothAssignments_1.[Employee#]) AND (WeldBoothAssignments.Booth =
WeldBoothAssignments_1.Booth)
WHERE (((WeldBoothAssignments.Timestamp) Like "*10/22/2009*") AND
((WeldBoothAssignments.BoothStatus)="INBooth") AND
((WeldBoothAssignments_1.BoothStatus)="OutofBooth") AND
((WeldBoothAssignments_1.ID)= (select min([WeldBoothAssignments].[ID]) from
WeldBoothAssignments
WHERE (WeldBoothAssignments.[Employee#] =
WeldBoothAssignments_1.[Employee#]) AND (WeldBoothAssignments.Booth =
WeldBoothAssignments_1.Booth) AND (WeldBoothAssignments_1.ID >
[WeldBoothAssignments].[ID]) )));




--
Daryl S


gcutter said:
I have a table the I am pulling from like below:
ID Employee# Booth Timestamp BoothStatus
841 414 Booth05 10/22/2009 06:58:31 INBooth
844 414 Booth05 10/22/2009 07:35:37 OutofBooth
845 414 Booth06 10/22/2009 07:35:49 INBooth
846 414 Booth06 10/22/2009 09:12:49 OutofBooth
847 414 Booth09 10/22/2009 09:13:01 INBooth
854 414 Booth09 10/22/2009 10:53:03 OutofBooth
855 414 Booth05 10/22/2009 10:53:16 INBooth
860 414 Booth05 10/22/2009 12:22:43 OutofBooth
866 414 Booth05 10/22/2009 12:35:31 INBooth
893 414 Booth05 10/22/2009 15:15:07 OutofBooth

Query:
SELECT WeldBoothAssignments.ID, WeldBoothAssignments.[Employee#],
WeldBoothAssignments.Booth, WeldBoothAssignments.Timestamp AS Start,
WeldBoothAssignments_1.Timestamp AS [End]
FROM WeldBoothAssignments INNER JOIN WeldBoothAssignments AS
WeldBoothAssignments_1 ON (WeldBoothAssignments.[Employee#] =
WeldBoothAssignments_1.[Employee#]) AND (WeldBoothAssignments.Booth =
WeldBoothAssignments_1.Booth)
WHERE (((WeldBoothAssignments.Timestamp) Like "*10/22/2009*") AND
((WeldBoothAssignments.BoothStatus)="INBooth") AND
((WeldBoothAssignments_1.BoothStatus)="OutofBooth") AND
((WeldBoothAssignments_1.ID)>[WeldBoothAssignments].[ID]));

Looking to get:
ID Employee#Booth Start End
841 414 Booth05 10/22/2009 06:58:31 10/22/2009 07:35:37
845 414 Booth06 10/22/2009 07:35:49 10/22/2009 09:12:49
847 414 Booth09 10/22/2009 09:13:01 10/22/2009 10:53:03
855 414 Booth05 10/22/2009 10:53:16 10/22/2009 12:22:43
866 414 Booth05 10/22/2009 12:35:31 10/22/2009 15:15:07

What I am getting:
ID Employee#Booth Start End
841 414 Booth05 10/22/2009 06:58:31 10/22/2009 15:15:07
841 414 Booth05 10/22/2009 06:58:31 10/22/2009 12:22:43
841 414 Booth05 10/22/2009 06:58:31 10/22/2009 07:35:37
845 414 Booth06 10/22/2009 07:35:49 10/22/2009 09:12:49
847 414 Booth09 10/22/2009 09:13:01 10/22/2009 10:53:03
855 414 Booth05 10/22/2009 10:53:16 10/22/2009 15:15:07
855 414 Booth05 10/22/2009 10:53:16 10/22/2009 12:22:43
866 414 Booth05 10/22/2009 12:35:31 10/22/2009 15:15:07
Multiples of Line 841 & 855 because they clocked in & out of same booth back
to back.

Thanks in Advanced,
Greg
Click to expand...
 
Back
Top