Find Duplicataes within Date Range

[JulioL]

I have a table in the following format:

ID,EmployeeNumber,StartDate,EndDate
234332,205,2/1/2007,7/1/2007
239053,203,2/1/2007,NULL
295382,204,4/29/2007,NULL
288392,205,6/23/2007,NULL
 

All IDs are unique.
An Employee Number should be assigned to a single ID for every calendar day (e.g. Employee Number 205 should only be assigned to one given ID for any given date.).

I'd like to create a query in Access that will look for overlapping Employee Number assignments and list for me what dates they are overllaping.

For example:
ID,EmployeeNumber,StartDateOverlap,EndDateOverlap
234332,205,6/23/2007,7/1/2007
288392,205,6/23/2007,7/1/2007

Or...
Date,EmployeeNumber,ID
6/23/2007,205,234332
6/23/2007,205,288392
6/24/2007,205,234332
6/24/2007,205,288392
etc.

Is there a simple way to get a query set up for this? I'm definitely having some trouble wrapping my head around it as I'm having trouble figuring out how to work with the date ranges to look for dupes.

-J

[ADezii]

I have a table in the following format:

ID,EmployeeNumber,StartDate,EndDate
234332,205,2/1/2007,7/1/2007
239053,203,2/1/2007,NULL
295382,204,4/29/2007,NULL
288392,205,6/23/2007,NULL
 

All IDs are unique.
An Employee Number should be assigned to a single ID for every calendar day (e.g. Employee Number 205 should only be assigned to one given ID for any given date.).

I'd like to create a query in Access that will look for overlapping Employee Number assignments and list for me what dates they are overllaping.

For example:
ID,EmployeeNumber,StartDateOverlap,EndDateOverlap
234332,205,6/23/2007,7/1/2007
288392,205,6/23/2007,7/1/2007

Or...
Date,EmployeeNumber,ID
6/23/2007,205,234332
6/23/2007,205,288392
6/24/2007,205,234332
6/24/2007,205,288392
etc.

Is there a simple way to get a query set up for this? I'm definitely having some trouble wrapping my head around it as I'm having trouble figuring out how to work with the date ranges to look for dupes.

-J

This will be a good start for you:

SELECT EmployeeNumber, ID, StartDate, EndDate
FROM tblAssignments
WHERE EmployeeNumber In (SELECT [EmployeeNumber] FROM [tblAssignments] As Tmp GROUP BY [EmployeeNumber] HAVING Count(*)>1 )
ORDER BY tblAssignments.EmployeeNumber;
 

[JulioL]

I have a table in the following format:

ID,EmployeeNumber,StartDate,EndDate
234332,205,2/1/2007,7/1/2007
239053,203,2/1/2007,NULL
295382,204,4/29/2007,NULL
288392,205,6/23/2007,NULL
 

Let me clarify the example a little bit. Here's a bit more data that may be found in the table:

ID,EmployeeNumber,StartDate,EndDate
234332,205,2/1/2007,7/1/2007
239053,203,2/1/2007,NULL
295382,204,4/29/2007,NULL
288392,205,6/23/2007,NULL
289553,206,2/1/2007,7/1/2007
928602,206,7/2/2007,NULL
 

You'll notice two EmployeeNumber values are dupes, 205 and 206. In the case of EmployeeNumber 206, you have no overlap in the date period. 206 was used from 2/1 to 7/1 and reused for 7/2 forward.

I'd like to catch the cases where there's an overlap in EmployeeNumbers based on date range. For example, 205 in this example shows to be used both by 234332 and 288392 from 6/23 to 7/1. I don't want to catch those cases where there's no date overlap (such as in EmployeeNumber 206).

[ADezii]

Let me clarify the example a little bit. Here's a bit more data that may be found in the table:

ID,EmployeeNumber,StartDate,EndDate
234332,205,2/1/2007,7/1/2007
239053,203,2/1/2007,NULL
295382,204,4/29/2007,NULL
288392,205,6/23/2007,NULL
289553,206,2/1/2007,7/1/2007
928602,206,7/2/2007,NULL
 

You'll notice two EmployeeNumber values are dupes, 205 and 206. In the case of EmployeeNumber 206, you have no overlap in the date period. 206 was used from 2/1 to 7/1 and reused for 7/2 forward.

I'd like to catch the cases where there's an overlap in EmployeeNumbers based on date range. For example, 205 in this example shows to be used both by 234332 and 288392 from 6/23 to 7/1. I don't want to catch those cases where there's no date overlap (such as in EmployeeNumber 206).

1. I really do not think that this can be done via SQL, but I'll hang around and let the real SQL Experts have a look at it. There may, however, be a viable code (VBA) solution but I'll hold off on that also.
2. 1 very important question, especially if you decide on a code alternative, is there a possibility that there can be more than 2 dupes? If there is this possibility, it adds to the complexity significantly.

[JulioL]

1. I really do not think that this can be done via SQL, but I'll hang around and let the real SQL Experts have a look at it. There may, however, be a viable code (VBA) solution but I'll hold off on that also.
2. 1 very important question, especially if you decide on a code alternative, is there a possibility that there can be more than 2 dupes? If there is this possibility, it adds to the complexity significantly.

I appreciate you lending a hand on this.

To answer your question, yes, there is a possibility of more than 2 dupes.

I hate dealing with mismanaged data. :(