By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,124 Members | 2,208 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,124 IT Pros & Developers. It's quick & easy.

Find Duplicataes within Date Range

P: 3
I have a table in the following format:

Expand|Select|Wrap|Line Numbers
  1. ID,EmployeeNumber,StartDate,EndDate
  2. 234332,205,2/1/2007,7/1/2007
  3. 239053,203,2/1/2007,NULL
  4. 295382,204,4/29/2007,NULL
  5. 288392,205,6/23/2007,NULL
  6.  
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
Aug 5 '07 #1
Share this Question
Share on Google+
4 Replies


ADezii
Expert 5K+
P: 8,669
I have a table in the following format:

Expand|Select|Wrap|Line Numbers
  1. ID,EmployeeNumber,StartDate,EndDate
  2. 234332,205,2/1/2007,7/1/2007
  3. 239053,203,2/1/2007,NULL
  4. 295382,204,4/29/2007,NULL
  5. 288392,205,6/23/2007,NULL
  6.  
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT EmployeeNumber, ID, StartDate, EndDate
  2. FROM tblAssignments
  3. WHERE EmployeeNumber In (SELECT [EmployeeNumber] FROM [tblAssignments] As Tmp GROUP BY [EmployeeNumber] HAVING Count(*)>1 )
  4. ORDER BY tblAssignments.EmployeeNumber;
  5.  
Aug 5 '07 #2

P: 3
I have a table in the following format:

Expand|Select|Wrap|Line Numbers
  1. ID,EmployeeNumber,StartDate,EndDate
  2. 234332,205,2/1/2007,7/1/2007
  3. 239053,203,2/1/2007,NULL
  4. 295382,204,4/29/2007,NULL
  5. 288392,205,6/23/2007,NULL
  6.  
Let me clarify the example a little bit. Here's a bit more data that may be found in the table:

Expand|Select|Wrap|Line Numbers
  1. ID,EmployeeNumber,StartDate,EndDate
  2. 234332,205,2/1/2007,7/1/2007
  3. 239053,203,2/1/2007,NULL
  4. 295382,204,4/29/2007,NULL
  5. 288392,205,6/23/2007,NULL
  6. 289553,206,2/1/2007,7/1/2007
  7. 928602,206,7/2/2007,NULL
  8.  
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).
Aug 6 '07 #3

ADezii
Expert 5K+
P: 8,669
Let me clarify the example a little bit. Here's a bit more data that may be found in the table:

Expand|Select|Wrap|Line Numbers
  1. ID,EmployeeNumber,StartDate,EndDate
  2. 234332,205,2/1/2007,7/1/2007
  3. 239053,203,2/1/2007,NULL
  4. 295382,204,4/29/2007,NULL
  5. 288392,205,6/23/2007,NULL
  6. 289553,206,2/1/2007,7/1/2007
  7. 928602,206,7/2/2007,NULL
  8.  
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.
Aug 6 '07 #4

P: 3
  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. :(
Aug 6 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.