Query to find "almost" duplicates

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

 

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

 

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

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

 

[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.

 

[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.