Duplicate invoices with different pay key

[newkid]

Hello Everyone,

I have a tabel that has VendorID, InvNo, PayKey.
The vendor can have duplicate invoices if the paykey is the the same.
The vendor cannot have duplcate invoices if the payke is not the same.
EXAMPLE:

Vendor InvNo PayKey
13579 TTP156 000679
13579 TTP156 000679
13579 TTP156 000680 DUPLICATE

I did a find duplicate query on the invoice number, but I need to find
out if the MBSkey is different.

I'm stumped.
I would appreciate any help.

 

[KARL DEWEY]

Try this --
Newkid_1 ----
SELECT newkid.Vendor, newkid.InvNo, newkid.PayKey
FROM newkid
GROUP BY newkid.Vendor, newkid.InvNo, newkid.PayKey;

SELECT Newkid_1.Vendor, Newkid_1.InvNo, Count(Newkid_1.PayKey) AS
CountOfPayKey
FROM Newkid_1
GROUP BY Newkid_1.Vendor, Newkid_1.InvNo
HAVING (((Count(Newkid_1.PayKey))>1));

 

[newkid]

Try this --
Newkid_1 ----
SELECT newkid.Vendor, newkid.InvNo, newkid.PayKey
FROM newkid
GROUP BY newkid.Vendor, newkid.InvNo, newkid.PayKey;

SELECT Newkid_1.Vendor, Newkid_1.InvNo, Count(Newkid_1.PayKey) AS
CountOfPayKey
FROM Newkid_1
GROUP BY Newkid_1.Vendor, Newkid_1.InvNo
HAVING (((Count(Newkid_1.PayKey))>1));

--
KARL DEWEY
Build a little - Test a little









- Show quoted text -

Thank you for the reply Karl. Unfortunately that finds the matching
pay keys with the duplicate invoice. I need it to find the unmatching
pay key with the duplicate invoice. Any other suggestions?

Thanks

 

[John Spencer]

The following query could work for you.

SELECT Vendor, InvNo, Paykey
FROM YourTable INNER JOIN
(SELECT Vendor, InvNo
FROM (
SELECT DISTINCT Vendor, InvNo, PayKey
FROM YourTable)
GROUP BY Vendor, InvNo
HAVING Count(PayKey) > 1)) as T2
ON YourTable.Vendor = T2.Vendor
And YourTable.InvNo = T2.InvNo

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[newkid]

The following query could work for you.

SELECT Vendor, InvNo, Paykey
FROM YourTable INNER JOIN
(SELECT Vendor, InvNo
FROM (
SELECT DISTINCT Vendor, InvNo, PayKey
FROM YourTable)
GROUP BY Vendor, InvNo
HAVING Count(PayKey) > 1)) as T2
ON YourTable.Vendor = T2.Vendor
And YourTable.InvNo = T2.InvNo

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.










- Show quoted text -

Thanks John,
I feel a little stupid here, but where does my inner join with T2 come
in?

 

[KARL DEWEY]

What about this ---
SELECT newkid.Vendor, newkid.InvNo, newkid.PayKey, Count(newkid.PayKey) AS
CountOfPayKey
FROM newkid
GROUP BY newkid.Vendor, newkid.InvNo, newkid.PayKey;

 

[John Spencer]

It's easier to understand if we break this down into three queries.

SELECT DISTINCT Vendor, InvNo, PayKey FROM YourTable

The above query gives you a list of the distinct combinations of Vendor,
InvNo, and Paykey

Now using that (save as qOne) we will identify those Vendor and InvNo that
have more than one PayKey

SELECT Vendor, InvNo
FROM qOne
GROUP BY Vendor, InvNo
HAVING Count(PayKey) > 1

And using that (saved as q2) we can link to the original table and return
those records where we have identical Vendor and InvNo and different paykeys

SELECT Vendor, InvNo, Paykey
FROM YourTable INNER JOIN Q2
ON YourTable.Vendor = Q2.Vendor
and YourTable.InvNo = Q2.InvNo

The problem here is how you specify which paykey is the "correct" one and
which are the "incorrect" one(s), so I simply return all of those involved.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[newkid]

It's easier to understand if we break this down into three queries.

SELECT DISTINCT Vendor, InvNo, PayKey FROM YourTable

The above query gives you a list of the distinct combinations of Vendor,
InvNo, and Paykey

Now using that (save as qOne) we will identify those Vendor and InvNo that
have more than one PayKey

SELECT Vendor, InvNo
FROM qOne
GROUP BY Vendor, InvNo
HAVING Count(PayKey) > 1

And using that (saved as q2) we can link to the original table and return
those records where we have identical Vendor and InvNo and different paykeys

SELECT Vendor, InvNo, Paykey
FROM YourTable INNER JOIN Q2
ON YourTable.Vendor = Q2.Vendor
and YourTable.InvNo = Q2.InvNo

The problem here is how you specify which paykey is the "correct" one and
which are the "incorrect" one(s), so I simply return all of those involved.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.







- Show quoted text -

You're absolutely right. I don't think I will be able to solve this
one.
Thanks so John and Karl for your help.

 

[John Spencer]

How do you (as a human) know which paykeys are valid for the Vendor and
InvNo? If you have to look them up on a paper record then there is no
solution to solving which are invalid and which are valid using the data in
the database.

If you use the rule that the paykey which appears most often is the valid
one and any others are invalid, then there is a solution to show only those
which MIGHT be invalid. But how would you handle it if two paykeys for
Vendor, InvNo appeared 1 time each?

The query I proposed should show all records where a Vendor, InvNo has more
than one paykey value associated with the combination.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..