Duplicate Query but with small Time Variance

[Chris]

Hello,

I have a table with the following fields:

Date
Time
Telephone Number
Credit Card Number
Expiry Date

I need to find all duplicate records with the following detail:

Date is duplicate
Telephone Number is Duplicate
Credit Card Number is Duplicate
Expiry Date is Duplicate

AND there CAN be a variance with the time (+/- 5 minutes).

We consider a duplicate record to have the first 4 fields identical and the
time to be within 5 minutes.

Is this possible? The duplicate query for the first 4 fields being
duplicates is fine, however, I'm uncertain if I can specify the time varience
in this query.

Thanks for any assistance!

 

[KARL DEWEY]

Try this using your table name --
SELECT ChrisCredit.Date, ChrisCredit.[Telephone Number], ChrisCredit.[Credit
Card Number], ChrisCredit.[Expiry Date], ChrisCredit.Time AS [Entry Time]
FROM ChrisCredit INNER JOIN ChrisCredit AS ChrisCredit_1 ON
(ChrisCredit.[Expiry Date] = ChrisCredit_1.[Expiry Date]) AND
(ChrisCredit.[Credit Card Number] = ChrisCredit_1.[Credit Card Number]) AND
(ChrisCredit.[Telephone Number] = ChrisCredit_1.[Telephone Number]) AND
(ChrisCredit.Date = ChrisCredit_1.Date)
GROUP BY ChrisCredit.Date, ChrisCredit.[Telephone Number],
ChrisCredit.[Credit Card Number], ChrisCredit.[Expiry Date],
ChrisCredit.Time, IIf([ChrisCredit].[Time] Between
DateAdd("n",-5,[ChrisCredit_1].[Time]) And
DateAdd("n",+5,[ChrisCredit_1].[Time]),[ChrisCredit].[Time],0)
HAVING (((Count(ChrisCredit.[Credit Card Number]))>1));

 

[Chris]

Thanks Karl!

This worked great.

I forgot one part that is causing erroneous data to be included.

This data covers multiple months and it is very likely that this credit card
number will be duplicated month over month (ie. the customer uses the same
credit card number, phone number and expiry date each time they make a
payment).

So, when this query does the count of the credit card number, it is finding
ALL instances of this credit card number (regardless of the month they used
it) and not just the ones within this 5 minute increment that was a duplicate
payment.

I've been playing around with a few solutions but haven't come up with
anything yet.

Any ideas?

Thanks Karl!

Chris

Try this using your table name --
SELECT ChrisCredit.Date, ChrisCredit.[Telephone Number], ChrisCredit.[Credit
Card Number], ChrisCredit.[Expiry Date], ChrisCredit.Time AS [Entry Time]
FROM ChrisCredit INNER JOIN ChrisCredit AS ChrisCredit_1 ON
(ChrisCredit.[Expiry Date] = ChrisCredit_1.[Expiry Date]) AND
(ChrisCredit.[Credit Card Number] = ChrisCredit_1.[Credit Card Number]) AND
(ChrisCredit.[Telephone Number] = ChrisCredit_1.[Telephone Number]) AND
(ChrisCredit.Date = ChrisCredit_1.Date)
GROUP BY ChrisCredit.Date, ChrisCredit.[Telephone Number],
ChrisCredit.[Credit Card Number], ChrisCredit.[Expiry Date],
ChrisCredit.Time, IIf([ChrisCredit].[Time] Between
DateAdd("n",-5,[ChrisCredit_1].[Time]) And
DateAdd("n",+5,[ChrisCredit_1].[Time]),[ChrisCredit].[Time],0)
HAVING (((Count(ChrisCredit.[Credit Card Number]))>1));

--
KARL DEWEY
Build a little - Test a little

Hello,

I have a table with the following fields:

Date
Time
Telephone Number
Credit Card Number
Expiry Date

I need to find all duplicate records with the following detail:

Date is duplicate
Telephone Number is Duplicate
Credit Card Number is Duplicate
Expiry Date is Duplicate

AND there CAN be a variance with the time (+/- 5 minutes).

We consider a duplicate record to have the first 4 fields identical and the
time to be within 5 minutes.

Is this possible? The duplicate query for the first 4 fields being
duplicates is fine, however, I'm uncertain if I can specify the time varience
in this query.

Thanks for any assistance!

 

[KARL DEWEY]

You probably would not have the problem if you used one field for date and
time.
--
KARL DEWEY
Build a little - Test a little

Thanks Karl!

This worked great.

I forgot one part that is causing erroneous data to be included.

This data covers multiple months and it is very likely that this credit card
number will be duplicated month over month (ie. the customer uses the same
credit card number, phone number and expiry date each time they make a
payment).

So, when this query does the count of the credit card number, it is finding
ALL instances of this credit card number (regardless of the month they used
it) and not just the ones within this 5 minute increment that was a duplicate
payment.

I've been playing around with a few solutions but haven't come up with
anything yet.

Any ideas?

Thanks Karl!

Chris

Try this using your table name --
SELECT ChrisCredit.Date, ChrisCredit.[Telephone Number], ChrisCredit.[Credit
Card Number], ChrisCredit.[Expiry Date], ChrisCredit.Time AS [Entry Time]
FROM ChrisCredit INNER JOIN ChrisCredit AS ChrisCredit_1 ON
(ChrisCredit.[Expiry Date] = ChrisCredit_1.[Expiry Date]) AND
(ChrisCredit.[Credit Card Number] = ChrisCredit_1.[Credit Card Number]) AND
(ChrisCredit.[Telephone Number] = ChrisCredit_1.[Telephone Number]) AND
(ChrisCredit.Date = ChrisCredit_1.Date)
GROUP BY ChrisCredit.Date, ChrisCredit.[Telephone Number],
ChrisCredit.[Credit Card Number], ChrisCredit.[Expiry Date],
ChrisCredit.Time, IIf([ChrisCredit].[Time] Between
DateAdd("n",-5,[ChrisCredit_1].[Time]) And
DateAdd("n",+5,[ChrisCredit_1].[Time]),[ChrisCredit].[Time],0)
HAVING (((Count(ChrisCredit.[Credit Card Number]))>1));

--
KARL DEWEY
Build a little - Test a little

Hello,

I have a table with the following fields:

Date
Time
Telephone Number
Credit Card Number
Expiry Date

I need to find all duplicate records with the following detail:

Date is duplicate
Telephone Number is Duplicate
Credit Card Number is Duplicate
Expiry Date is Duplicate

AND there CAN be a variance with the time (+/- 5 minutes).

We consider a duplicate record to have the first 4 fields identical and the
time to be within 5 minutes.

Is this possible? The duplicate query for the first 4 fields being
duplicates is fine, however, I'm uncertain if I can specify the time varience
in this query.

Thanks for any assistance!