Finding Duplicate entries using two fields

[DavidS]

I need to find transactions where the same credit card number was used for
two different customers.
My table lists all of the pending orders: Ordernum, Custnum, cardnum, date,
etc.
One customer may have several orders and use the same credit card number.
However, sometimes the operator may accidentally enter their card number on
another customer's order. I need to search the table for these errors before
processing the orders.
I've used the wizard successfully to show all of the orders where the same
cardnumber was used... however, I can't figure out how to filter out the
orders where the same customer number is used.

Thanks in advance for your help.

 

[Krizhek]

In design view you will need to set criteria for the customer number field to
<>[Enter Customer Number]

this will cause the query to prompt you for the Customer Number and then it
will filter out that Customer number.

Here is an sql example.

SELECT [tbl example].Custnum, [tbl example].Cardnum, [tbl example].Date
FROM [tbl example]
WHERE ((([tbl example].Custnum)<>[Enter Customer Number]) AND (([tbl
example].Cardnum)=[Enter Credit Card]));

Hope this works for you.

 

[DavidS]

When you have a few hundred records, this is almost impossible. I need the
query to automatically exclude duplicate credit card numbers where the
customer is the same.

Any other ideas?

In design view you will need to set criteria for the customer number field to
<>[Enter Customer Number]

this will cause the query to prompt you for the Customer Number and then it
will filter out that Customer number.

Here is an sql example.

SELECT [tbl example].Custnum, [tbl example].Cardnum, [tbl example].Date
FROM [tbl example]
WHERE ((([tbl example].Custnum)<>[Enter Customer Number]) AND (([tbl
example].Cardnum)=[Enter Credit Card]));

Hope this works for you.

I need to find transactions where the same credit card number was used for
two different customers.
My table lists all of the pending orders: Ordernum, Custnum, cardnum, date,
etc.
One customer may have several orders and use the same credit card number.
However, sometimes the operator may accidentally enter their card number on
another customer's order. I need to search the table for these errors before
processing the orders.
I've used the wizard successfully to show all of the orders where the same
cardnumber was used... however, I can't figure out how to filter out the
orders where the same customer number is used.

Thanks in advance for your help.

 

[DavidS]

Thanks for everyone's consideration... I assume that since I had no answers
that you considered it impossible. But it is possible and I have done it!

The process is as follows:

1st make a duplicate query on the one field (cardnum) and restrict the
results by date (e.g. ">Date()-1")

My SQL is:
SELECT cms.cardnum, cms.order, cms.custnum, cms.odr_date, cms.paymethod
FROM cms
WHERE (((cms.cardnum) In (SELECT [cardnum] FROM [cms] As Tmp GROUP BY
[cardnum] HAVING Count(*)>1 )) AND ((cms.odr_date)>Date()-5) AND
((cms.paymethod)="CC"))
ORDER BY cms.cardnum, cms.order;
(this query was named CmsDuplicateB)


Then make a query that compares the results of the first query to the table
and identifies orders that are in the 1st query that have the same cardnum
but do not have the same custnum.

My SQL is:
SELECT cms.cardnum, cms.order AS ORD1, CmsDuplicateB.order AS ORD2,
cms.custnum AS Cust1, cust.lastname AS L1, CmsDuplicateB.custnum AS Cust2,
cust_1.lastname AS L2, cms.odr_date, cms.paymethod
FROM (cust RIGHT JOIN cms ON cust.custnum = cms.custnum) RIGHT JOIN
(CmsDuplicateB LEFT JOIN cust AS cust_1 ON CmsDuplicateB.custnum =
cust_1.custnum) ON cms.cardnum = CmsDuplicateB.cardnum
WHERE (((cms.cardnum)>"0") AND ((cms.custnum)<>[CmsDuplicateB].[custnum])
AND ((cms.odr_date)>Date()-5) AND ((cms.paymethod)="CC"))
ORDER BY cms.cardnum, cms.order, CmsDuplicateB.order;

Note that in this query I added another table (cust) to obtain the
customer's last name for orders on the cms table and an alias of cust
(cust_1) to obtain the customer's last name for orders on the duplicate query.

The key to this working is "(cms.custnum)<>[CmsDuplicateB].[custnum])" which
causes the query to exclude all of the duplicate entries that have the same
custnum.

Hope this helps someone else.