Query to find "almost" duplicates

  • Thread starter Thread starter Angela
  • Start date Start date
A

Angela

I have used the "duplicate query wizard" to find training records where the
Employee, Coursenumber, Training Day were identical (to weed out duplicate
records). I now need to search for the same thing but where the Training Day
is +/- 1 Day (since some of the duplicate records were entered on consecutive
days). Does anyone know how I can create such a query?
 
I have used the "duplicate query wizard" to find training records where the
Employee, Coursenumber, Training Day were identical (to weed out duplicate
records). I now need to search for the same thing but where the Training Day
is +/- 1 Day (since some of the duplicate records were entered on consecutive
days). Does anyone know how I can create such a query?
Click to expand...

Use a query joining the two tables by Employee and Coursenumber. On one
table's Training Day field's Criteria put
= DateAdd("d", -1, [othertable].[Training Day]) AND <= DateAdd("d", 1, [othertable].[Training Day])
Click to expand...
 
These records exist in one table. When you say 'join the two tables', do you
mean join the table to itself on these two fields?

John W. Vinson said:
I have used the "duplicate query wizard" to find training records where the
Employee, Coursenumber, Training Day were identical (to weed out duplicate
records). I now need to search for the same thing but where the Training Day
is +/- 1 Day (since some of the duplicate records were entered on consecutive
days). Does anyone know how I can create such a query?
Click to expand...

Use a query joining the two tables by Employee and Coursenumber. On one
table's Training Day field's Criteria put
= DateAdd("d", -1, [othertable].[Training Day]) AND <= DateAdd("d", 1, [othertable].[Training Day])
Click to expand...
Click to expand...
 
These records exist in one table. When you say 'join the two tables', do you
mean join the table to itself on these two fields?
Click to expand...

Yes. Sorry, didn't catch that this was a self join.
 
That worked quite well. Since it was a self join, I modified it to:
= DateAdd("d", -1, [othertable].[Training Day]) AND < [othertable].[Training Day]
Click to expand...

which eliminated the duplicate matches and also the cases where they matched
exactly (the latter of which returned all the records in the table). Thank
you very much! I was really stumped.
 
Back
Top