duplicate +/- query

  • Thread starter Thread starter Anna
  • Start date Start date
A

Anna

Hi,

I would like to run a duplicate query on two fields:
exact duplicate for "ID" and + or - 3days for "Date". How
can I get the Date part?

Is this possible?

Many thanks,
Anna
 
My table includes the fields ID and DOB, and I want to
find duplicates where "duplicate" is defined as:
ID is equal and DOB is + or - 3days
For example:
ID DOB
45 17/10/56
45 15/10/56
45 25/10/56

would return the first two records (that have identical
IDs and DOBs within 3 days of each other), but not the
third record.

Is this any clearer? Thanks for your help (and patience!).

Anna
 
Assuming your table is called tblDOB, and you have a unique identifier field
called uniqueID, and your DOB's have no time component, try:

SELECT DISTINCT tblDOB.ID, tblDOB.DOB, tblDOB_1.DOB
FROM tblDOB INNER JOIN tblDOB AS tblDOB_1 ON tblDOB.ID = tblDOB_1.ID
WHERE ((([tblDOB_1].[DOB]-[tblDOB].[DOB])>=0 And
([tblDOB_1].[DOB]-[tblDOB].[DOB])<=3) AND
((tblDOB_1.UniqueID)<>[tblDOB].[UniqueID]));
 
That's great - thanks very much!

Cheers,
Anna
-----Original Message-----
Assuming your table is called tblDOB, and you have a unique identifier field
called uniqueID, and your DOB's have no time component, try:

SELECT DISTINCT tblDOB.ID, tblDOB.DOB, tblDOB_1.DOB
FROM tblDOB INNER JOIN tblDOB AS tblDOB_1 ON tblDOB.ID = tblDOB_1.ID
WHERE ((([tblDOB_1].[DOB]-[tblDOB].[DOB])>=0 And
([tblDOB_1].[DOB]-[tblDOB].[DOB])<=3) AND
((tblDOB_1.UniqueID)<>[tblDOB].[UniqueID]));


Anna said:
My table includes the fields ID and DOB, and I want to
find duplicates where "duplicate" is defined as:
ID is equal and DOB is + or - 3days
For example:
ID DOB
45 17/10/56
45 15/10/56
45 25/10/56

would return the first two records (that have identical
IDs and DOBs within 3 days of each other), but not the
third record.

Is this any clearer? Thanks for your help (and patience!).

Anna
Click to expand...


.
Click to expand...
 
Back
Top