Query to find "almost" duplicates

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?
 
J

John W. Vinson

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?

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])
 
A

Angela

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?

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])
 
J

John W. Vinson

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?

Yes. Sorry, didn't catch that this was a self join.
 
A

Angela

That worked quite well. Since it was a self join, I modified it to:
= DateAdd("d", -1, [othertable].[Training Day]) AND < [othertable].[Training Day]

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top