How to find duplicate payments

[utahbiker7]

I have imported to 2007 Access our accounts payable history. I would like
to analysis the data to determine if we have made duplicate payments to a
vendor. I would like to query the database on vendor number. If within
the vendor number an invoice amount is identical I would like it flagged.
I would also like to narrow those records down to those have similar invoice
numbers. I have found that sometimes the processing of payments is forced
by changing the invoice number - like adding an additional digit to override
the software's controls and thus making a duplicate payment to a vendor.
So, if anyone can help me with a query to identify duplicate payments to a
vendor, I would appreciate it. It has been a long time since I've used
access but I remember at one time it could identify dups... thanks.

 

[John Spencer]

This might get you started.

SELECT AP.VendorNumber, AP.AmountPaid
FROM [Your Table] as AP
INNER JOIN [Your Table] As Tmp
ON AP.VendorNumber = Tmp.VendorNumber
AND AP.AmountPaid = Tmp.AmountPaid
AND AP.InvoiceNumber LIKE Tmp.InvoiceNumber & "*"
WHERE Tmp.InvoiceNumber is Not Null
GROUP BY AP.VendorNumber, AP.AmountPaid
Having Count(*) > 1

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

 

[utahbiker7]

John,
I wish I was as familar with this program as you are. I do not know how to
execute these commands. I do not know how to inner join the files so the
LIKE comparison can be made on each vendors invoice number. Can you walk me
through how to do this in access?
Mark

This might get you started.

SELECT AP.VendorNumber, AP.AmountPaid
FROM [Your Table] as AP
INNER JOIN [Your Table] As Tmp
ON AP.VendorNumber = Tmp.VendorNumber
AND AP.AmountPaid = Tmp.AmountPaid
AND AP.InvoiceNumber LIKE Tmp.InvoiceNumber & "*"
WHERE Tmp.InvoiceNumber is Not Null
GROUP BY AP.VendorNumber, AP.AmountPaid
Having Count(*) > 1

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

I have imported to 2007 Access our accounts payable history. I would like
to analysis the data to determine if we have made duplicate payments to a
vendor. I would like to query the database on vendor number. If within
the vendor number an invoice amount is identical I would like it flagged.
I would also like to narrow those records down to those have similar invoice
numbers. I have found that sometimes the processing of payments is forced
by changing the invoice number - like adding an additional digit to override
the software's controls and thus making a duplicate payment to a vendor.
So, if anyone can help me with a query to identify duplicate payments to a
vendor, I would appreciate it. It has been a long time since I've used
access but I remember at one time it could identify dups... thanks.

 

[utahbiker7]

John,
I got the sql code entered as below: It narrows down the results from 400k
records to 41k. I don't believe they are all duplicates but it is hard to
see just what records it is choosing. The results only give me two fields -
the vendor number and invoice amount. Can you tell me how to get the other
fields to show with the results? I need the check_no, check_date and store
number to investigate the results to see if it is indeed a duplicate payment.
I appreciate your help.

SELECT AP.VENDOR_NUMBER, AP.INVOICE_AMOUNT
FROM AP INNER JOIN TMP ON (AP.INVOICE_NUM LIKE TMP.INVOICE_NUM & "*") AND
(AP.INVOICE_AMOUNT=TMP.INVOICE_AMOUNT) AND
(AP.VENDOR_NUMBER=TMP.VENDOR_NUMBER)
WHERE TMP.INVOICE_NUM IS NOT NULL
GROUP BY AP.VENDOR_NUMBER, AP.INVOICE_AMOUNT
HAVING COUNT(*)>1;

This might get you started.

SELECT AP.VendorNumber, AP.AmountPaid
FROM [Your Table] as AP
INNER JOIN [Your Table] As Tmp
ON AP.VendorNumber = Tmp.VendorNumber
AND AP.AmountPaid = Tmp.AmountPaid
AND AP.InvoiceNumber LIKE Tmp.InvoiceNumber & "*"
WHERE Tmp.InvoiceNumber is Not Null
GROUP BY AP.VendorNumber, AP.AmountPaid
Having Count(*) > 1

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

I have imported to 2007 Access our accounts payable history. I would like
to analysis the data to determine if we have made duplicate payments to a
vendor. I would like to query the database on vendor number. If within
the vendor number an invoice amount is identical I would like it flagged.
I would also like to narrow those records down to those have similar invoice
numbers. I have found that sometimes the processing of payments is forced
by changing the invoice number - like adding an additional digit to override
the software's controls and thus making a duplicate payment to a vendor.
So, if anyone can help me with a query to identify duplicate payments to a
vendor, I would appreciate it. It has been a long time since I've used
access but I remember at one time it could identify dups... thanks.

 

[John Spencer]

Ok, the next step is to use the query you have built to show all the records

SELECT AP.*
FROM AP
WHERE AP.Vendor_Number & Ap.Invoice_Amount in
(SELECT Q.Vendor_Number & Q.Invoice_Amount FROM TheQuery)

That will probably be slow.

But the real problem is still the Invoice_Number being similar. I think
I need to rethink this problem if you are going to get decent performance.

I'll try to post back later with a better solution.

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

John,
I got the sql code entered as below: It narrows down the results from 400k
records to 41k. I don't believe they are all duplicates but it is hard to
see just what records it is choosing. The results only give me two fields -
the vendor number and invoice amount. Can you tell me how to get the other
fields to show with the results? I need the check_no, check_date and store
number to investigate the results to see if it is indeed a duplicate payment.
I appreciate your help.

SELECT AP.VENDOR_NUMBER, AP.INVOICE_AMOUNT
FROM AP INNER JOIN TMP ON (AP.INVOICE_NUM LIKE TMP.INVOICE_NUM & "*") AND
(AP.INVOICE_AMOUNT=TMP.INVOICE_AMOUNT) AND
(AP.VENDOR_NUMBER=TMP.VENDOR_NUMBER)
WHERE TMP.INVOICE_NUM IS NOT NULL
GROUP BY AP.VENDOR_NUMBER, AP.INVOICE_AMOUNT
HAVING COUNT(*)>1;

This might get you started.

SELECT AP.VendorNumber, AP.AmountPaid
FROM [Your Table] as AP
INNER JOIN [Your Table] As Tmp
ON AP.VendorNumber = Tmp.VendorNumber
AND AP.AmountPaid = Tmp.AmountPaid
AND AP.InvoiceNumber LIKE Tmp.InvoiceNumber & "*"
WHERE Tmp.InvoiceNumber is Not Null
GROUP BY AP.VendorNumber, AP.AmountPaid
Having Count(*) > 1

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

I have imported to 2007 Access our accounts payable history. I would like
to analysis the data to determine if we have made duplicate payments to a
vendor. I would like to query the database on vendor number. If within
the vendor number an invoice amount is identical I would like it flagged.
I would also like to narrow those records down to those have similar invoice
numbers. I have found that sometimes the processing of payments is forced
by changing the invoice number - like adding an additional digit to override
the software's controls and thus making a duplicate payment to a vendor.
So, if anyone can help me with a query to identify duplicate payments to a
vendor, I would appreciate it. It has been a long time since I've used
access but I remember at one time it could identify dups... thanks.

 

[utahbiker7]

I have saved the results from the first script to "Q". I then typed the
query in but got this message - " Circular reference caused by Q "
So, I can't get this query to run. If I have misinterpreted you statements,
please let me know. I appreciate all of your help.

SELECT AP.*
FROM AP
WHERE AP.VENDOR_NUMBER AND AP.INVOICE_AMOUNT IN (SELECT Q.VENDOR_NUMBER AND
Q.INVOICE_AMOUNT FROM Q)

Ok, the next step is to use the query you have built to show all the records

SELECT AP.*
FROM AP
WHERE AP.Vendor_Number & Ap.Invoice_Amount in
(SELECT Q.Vendor_Number & Q.Invoice_Amount FROM TheQuery)

That will probably be slow.

But the real problem is still the Invoice_Number being similar. I think
I need to rethink this problem if you are going to get decent performance.

I'll try to post back later with a better solution.

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

John,
I got the sql code entered as below: It narrows down the results from 400k
records to 41k. I don't believe they are all duplicates but it is hard to
see just what records it is choosing. The results only give me two fields -
the vendor number and invoice amount. Can you tell me how to get the other
fields to show with the results? I need the check_no, check_date and store
number to investigate the results to see if it is indeed a duplicate payment.
I appreciate your help.

SELECT AP.VENDOR_NUMBER, AP.INVOICE_AMOUNT
FROM AP INNER JOIN TMP ON (AP.INVOICE_NUM LIKE TMP.INVOICE_NUM & "*") AND
(AP.INVOICE_AMOUNT=TMP.INVOICE_AMOUNT) AND
(AP.VENDOR_NUMBER=TMP.VENDOR_NUMBER)
WHERE TMP.INVOICE_NUM IS NOT NULL
GROUP BY AP.VENDOR_NUMBER, AP.INVOICE_AMOUNT
HAVING COUNT(*)>1;

This might get you started.

SELECT AP.VendorNumber, AP.AmountPaid
FROM [Your Table] as AP
INNER JOIN [Your Table] As Tmp
ON AP.VendorNumber = Tmp.VendorNumber
AND AP.AmountPaid = Tmp.AmountPaid
AND AP.InvoiceNumber LIKE Tmp.InvoiceNumber & "*"
WHERE Tmp.InvoiceNumber is Not Null
GROUP BY AP.VendorNumber, AP.AmountPaid
Having Count(*) > 1

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


utahbiker7 wrote:
I have imported to 2007 Access our accounts payable history. I would like
to analysis the data to determine if we have made duplicate payments to a
vendor. I would like to query the database on vendor number. If within
the vendor number an invoice amount is identical I would like it flagged.
I would also like to narrow those records down to those have similar invoice
numbers. I have found that sometimes the processing of payments is forced
by changing the invoice number - like adding an additional digit to override
the software's controls and thus making a duplicate payment to a vendor.
So, if anyone can help me with a query to identify duplicate payments to a
vendor, I would appreciate it. It has been a long time since I've used
access but I remember at one time it could identify dups... thanks.

 

[utahbiker7]

John,
If the report can give me identical invoice amounts within each vendor, I
can use it to scan for invoice numbers that look similiar. They usually
stand out anyway in that the invoice number will have -1 or -* or some other
character added to the invoice field to make Oracle accept it. This should
be sufficient if we can't programmatically identify those records within each
vendor that have similiar invoice numbers.
Thanks,
Mark

Ok, the next step is to use the query you have built to show all the records

SELECT AP.*
FROM AP
WHERE AP.Vendor_Number & Ap.Invoice_Amount in
(SELECT Q.Vendor_Number & Q.Invoice_Amount FROM TheQuery)

That will probably be slow.

But the real problem is still the Invoice_Number being similar. I think
I need to rethink this problem if you are going to get decent performance.

I'll try to post back later with a better solution.

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

John,
I got the sql code entered as below: It narrows down the results from 400k
records to 41k. I don't believe they are all duplicates but it is hard to
see just what records it is choosing. The results only give me two fields -
the vendor number and invoice amount. Can you tell me how to get the other
fields to show with the results? I need the check_no, check_date and store
number to investigate the results to see if it is indeed a duplicate payment.
I appreciate your help.

SELECT AP.VENDOR_NUMBER, AP.INVOICE_AMOUNT
FROM AP INNER JOIN TMP ON (AP.INVOICE_NUM LIKE TMP.INVOICE_NUM & "*") AND
(AP.INVOICE_AMOUNT=TMP.INVOICE_AMOUNT) AND
(AP.VENDOR_NUMBER=TMP.VENDOR_NUMBER)
WHERE TMP.INVOICE_NUM IS NOT NULL
GROUP BY AP.VENDOR_NUMBER, AP.INVOICE_AMOUNT
HAVING COUNT(*)>1;

This might get you started.

SELECT AP.VendorNumber, AP.AmountPaid
FROM [Your Table] as AP
INNER JOIN [Your Table] As Tmp
ON AP.VendorNumber = Tmp.VendorNumber
AND AP.AmountPaid = Tmp.AmountPaid
AND AP.InvoiceNumber LIKE Tmp.InvoiceNumber & "*"
WHERE Tmp.InvoiceNumber is Not Null
GROUP BY AP.VendorNumber, AP.AmountPaid
Having Count(*) > 1

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


utahbiker7 wrote:
I have imported to 2007 Access our accounts payable history. I would like
to analysis the data to determine if we have made duplicate payments to a
vendor. I would like to query the database on vendor number. If within
the vendor number an invoice amount is identical I would like it flagged.
I would also like to narrow those records down to those have similar invoice
numbers. I have found that sometimes the processing of payments is forced
by changing the invoice number - like adding an additional digit to override
the software's controls and thus making a duplicate payment to a vendor.
So, if anyone can help me with a query to identify duplicate payments to a
vendor, I would appreciate it. It has been a long time since I've used
access but I remember at one time it could identify dups... thanks.

 

[John Spencer]

Just started thinking about solutions that would be close, but not perfect.

SELECT AP.VendorNumber, AP.AmountPaid, AP.InvoiceNumber
FROM [Your Table] as AP
WHERE AP.VendorNumber in
(SELECT Tmp.VendorNumber, Tmp.AmountPaid
, Left(Tmp.InvoiceNumber,5)
FROM [Your Table] as Tmp
GROUP BY Tmp.VendorNumber, Tmp.AmountPaid
, Left(Tmp.InvoiceNumber,5)
HAVING Count(*) > 1
AND Tmp.AmountPaid = AP.AmountPaid
AND Left(Tmp.InvoiceNumber,5) = Left(AP.InvoiceNumber,5)

If I got that built correctly it should return duplicate records based
on Vendor Number, Amount and the first 5 characters of InvoiceNumber
being the same. Of course, you can change the 5 characters to 6 or 7 or
whatever the minimum length of the "normal" Invoice number is.

Hope this works for you. Sorry, it took so long for me to respond, but
I just was not seeing a PERFECT solution and then I figured that this
should let you get close.

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

John,
If the report can give me identical invoice amounts within each vendor, I
can use it to scan for invoice numbers that look similiar. They usually
stand out anyway in that the invoice number will have -1 or -* or some other
character added to the invoice field to make Oracle accept it. This should
be sufficient if we can't programmatically identify those records within each
vendor that have similiar invoice numbers.
Thanks,
Mark

Ok, the next step is to use the query you have built to show all the records

SELECT AP.*
FROM AP
WHERE AP.Vendor_Number & Ap.Invoice_Amount in
(SELECT Q.Vendor_Number & Q.Invoice_Amount FROM TheQuery)

That will probably be slow.

But the real problem is still the Invoice_Number being similar. I think
I need to rethink this problem if you are going to get decent performance.

I'll try to post back later with a better solution.

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

John,
I got the sql code entered as below: It narrows down the results from 400k
records to 41k. I don't believe they are all duplicates but it is hard to
see just what records it is choosing. The results only give me two fields -
the vendor number and invoice amount. Can you tell me how to get the other
fields to show with the results? I need the check_no, check_date and store
number to investigate the results to see if it is indeed a duplicate payment.
I appreciate your help.

SELECT AP.VENDOR_NUMBER, AP.INVOICE_AMOUNT
FROM AP INNER JOIN TMP ON (AP.INVOICE_NUM LIKE TMP.INVOICE_NUM & "*") AND
(AP.INVOICE_AMOUNT=TMP.INVOICE_AMOUNT) AND
(AP.VENDOR_NUMBER=TMP.VENDOR_NUMBER)
WHERE TMP.INVOICE_NUM IS NOT NULL
GROUP BY AP.VENDOR_NUMBER, AP.INVOICE_AMOUNT
HAVING COUNT(*)>1;


:

This might get you started.

SELECT AP.VendorNumber, AP.AmountPaid
FROM [Your Table] as AP
INNER JOIN [Your Table] As Tmp
ON AP.VendorNumber = Tmp.VendorNumber
AND AP.AmountPaid = Tmp.AmountPaid
AND AP.InvoiceNumber LIKE Tmp.InvoiceNumber & "*"
WHERE Tmp.InvoiceNumber is Not Null
GROUP BY AP.VendorNumber, AP.AmountPaid
Having Count(*) > 1

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


utahbiker7 wrote:
I have imported to 2007 Access our accounts payable history. I would like
to analysis the data to determine if we have made duplicate payments to a
vendor. I would like to query the database on vendor number. If within
the vendor number an invoice amount is identical I would like it flagged.
I would also like to narrow those records down to those have similar invoice
numbers. I have found that sometimes the processing of payments is forced
by changing the invoice number - like adding an additional digit to override
the software's controls and thus making a duplicate payment to a vendor.
So, if anyone can help me with a query to identify duplicate payments to a
vendor, I would appreciate it. It has been a long time since I've used
access but I remember at one time it could identify dups... thanks.

 

[utahbiker7]

John,
I have typed in the query per below. It gives me this error
"You have written a subquery that can return more than one field without
using the EXISTS reserved word in the main query's FROM clause. Revise the
SELECT statement of the subquery to request only one field."
I think I typed in the query correctly but I can't seem to get this
statement above to clear. Would you look at the query for me and see if it
can be changed to clear the above message?

Thanks,
Mark


SELECT AP.Vendor_Number, AP.Invoice_Amount, AP.Invoice_Num
FROM AP
WHERE AP.Vendor_Number in
(SELECT Tmp.Vendor_Number, Tmp.invoice_amount
, Left(Tmp.Invoice_Num,5)
FROM Tmp)
GROUP BY Tmp.Vendor_Number, Tmp.Invoice_Amount
, Left(Tmp.Invoice_Num,5)
HAVING Count(*) > 1
AND Tmp.Invoice_amount = AP.invoice_Amount
AND Left(Tmp.Invoice_Num,5) = Left(AP.Invoice_Num,5)

Just started thinking about solutions that would be close, but not perfect.

SELECT AP.VendorNumber, AP.AmountPaid, AP.InvoiceNumber
FROM [Your Table] as AP
WHERE AP.VendorNumber in
(SELECT Tmp.VendorNumber, Tmp.AmountPaid
, Left(Tmp.InvoiceNumber,5)
FROM [Your Table] as Tmp
GROUP BY Tmp.VendorNumber, Tmp.AmountPaid
, Left(Tmp.InvoiceNumber,5)
HAVING Count(*) > 1
AND Tmp.AmountPaid = AP.AmountPaid
AND Left(Tmp.InvoiceNumber,5) = Left(AP.InvoiceNumber,5)

If I got that built correctly it should return duplicate records based
on Vendor Number, Amount and the first 5 characters of InvoiceNumber
being the same. Of course, you can change the 5 characters to 6 or 7 or
whatever the minimum length of the "normal" Invoice number is.

Hope this works for you. Sorry, it took so long for me to respond, but
I just was not seeing a PERFECT solution and then I figured that this
should let you get close.

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

John,
If the report can give me identical invoice amounts within each vendor, I
can use it to scan for invoice numbers that look similiar. They usually
stand out anyway in that the invoice number will have -1 or -* or some other
character added to the invoice field to make Oracle accept it. This should
be sufficient if we can't programmatically identify those records within each
vendor that have similiar invoice numbers.
Thanks,
Mark

Ok, the next step is to use the query you have built to show all the records

SELECT AP.*
FROM AP
WHERE AP.Vendor_Number & Ap.Invoice_Amount in
(SELECT Q.Vendor_Number & Q.Invoice_Amount FROM TheQuery)

That will probably be slow.

But the real problem is still the Invoice_Number being similar. I think
I need to rethink this problem if you are going to get decent performance.

I'll try to post back later with a better solution.

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


utahbiker7 wrote:
John,
I got the sql code entered as below: It narrows down the results from 400k
records to 41k. I don't believe they are all duplicates but it is hard to
see just what records it is choosing. The results only give me two fields -
the vendor number and invoice amount. Can you tell me how to get the other
fields to show with the results? I need the check_no, check_date and store
number to investigate the results to see if it is indeed a duplicate payment.
I appreciate your help.

SELECT AP.VENDOR_NUMBER, AP.INVOICE_AMOUNT
FROM AP INNER JOIN TMP ON (AP.INVOICE_NUM LIKE TMP.INVOICE_NUM & "*") AND
(AP.INVOICE_AMOUNT=TMP.INVOICE_AMOUNT) AND
(AP.VENDOR_NUMBER=TMP.VENDOR_NUMBER)
WHERE TMP.INVOICE_NUM IS NOT NULL
GROUP BY AP.VENDOR_NUMBER, AP.INVOICE_AMOUNT
HAVING COUNT(*)>1;


:

This might get you started.

SELECT AP.VendorNumber, AP.AmountPaid
FROM [Your Table] as AP
INNER JOIN [Your Table] As Tmp
ON AP.VendorNumber = Tmp.VendorNumber
AND AP.AmountPaid = Tmp.AmountPaid
AND AP.InvoiceNumber LIKE Tmp.InvoiceNumber & "*"
WHERE Tmp.InvoiceNumber is Not Null
GROUP BY AP.VendorNumber, AP.AmountPaid
Having Count(*) > 1

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


utahbiker7 wrote:
I have imported to 2007 Access our accounts payable history. I would like
to analysis the data to determine if we have made duplicate payments to a
vendor. I would like to query the database on vendor number. If within
the vendor number an invoice amount is identical I would like it flagged.
I would also like to narrow those records down to those have similar invoice
numbers. I have found that sometimes the processing of payments is forced
by changing the invoice number - like adding an additional digit to override
the software's controls and thus making a duplicate payment to a vendor.
So, if anyone can help me with a query to identify duplicate payments to a
vendor, I would appreciate it. It has been a long time since I've used
access but I remember at one time it could identify dups... thanks.

 

[John Spencer]

My bad. I started to do this another way and then changed my method in
mid-thought. I forgot to clean up.

Try the following. It is basically an unmatched query as the unmatched query
wizard would create it.

SELECT AP.Vendor_Number, AP.Invoice_Amount, AP.Invoice_Num
FROM AP
WHERE AP.Vendor_Number in
(SELECT Tmp.Vendor_Number
FROM AP as Tmp
GROUP BY Tmp.Vendor_Number, Tmp.Invoice_Amount
, Left(Tmp.Invoice_Num,5)
HAVING Count(*) > 1
AND Tmp.Invoice_amount = AP.invoice_Amount
AND Left(Tmp.Invoice_Num,5) = Left(AP.Invoice_Num,5))



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

John,
I have typed in the query per below. It gives me this error
"You have written a subquery that can return more than one field without
using the EXISTS reserved word in the main query's FROM clause. Revise the
SELECT statement of the subquery to request only one field."
I think I typed in the query correctly but I can't seem to get this
statement above to clear. Would you look at the query for me and see if it
can be changed to clear the above message?

Thanks,
Mark


SELECT AP.Vendor_Number, AP.Invoice_Amount, AP.Invoice_Num
FROM AP
WHERE AP.Vendor_Number in
(SELECT Tmp.Vendor_Number, Tmp.invoice_amount
, Left(Tmp.Invoice_Num,5)
FROM Tmp)
GROUP BY Tmp.Vendor_Number, Tmp.Invoice_Amount
, Left(Tmp.Invoice_Num,5)
HAVING Count(*) > 1
AND Tmp.Invoice_amount = AP.invoice_Amount
AND Left(Tmp.Invoice_Num,5) = Left(AP.Invoice_Num,5)

Just started thinking about solutions that would be close, but not perfect.

SELECT AP.VendorNumber, AP.AmountPaid, AP.InvoiceNumber
FROM [Your Table] as AP
WHERE AP.VendorNumber in
(SELECT Tmp.VendorNumber, Tmp.AmountPaid
, Left(Tmp.InvoiceNumber,5)
FROM [Your Table] as Tmp
GROUP BY Tmp.VendorNumber, Tmp.AmountPaid
, Left(Tmp.InvoiceNumber,5)
HAVING Count(*) > 1
AND Tmp.AmountPaid = AP.AmountPaid
AND Left(Tmp.InvoiceNumber,5) = Left(AP.InvoiceNumber,5)

If I got that built correctly it should return duplicate records based
on Vendor Number, Amount and the first 5 characters of InvoiceNumber
being the same. Of course, you can change the 5 characters to 6 or 7 or
whatever the minimum length of the "normal" Invoice number is.

Hope this works for you. Sorry, it took so long for me to respond, but
I just was not seeing a PERFECT solution and then I figured that this
should let you get close.

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

John,
If the report can give me identical invoice amounts within each vendor, I
can use it to scan for invoice numbers that look similiar. They usually
stand out anyway in that the invoice number will have -1 or -* or some other
character added to the invoice field to make Oracle accept it. This should
be sufficient if we can't programmatically identify those records within each
vendor that have similiar invoice numbers.
Thanks,
Mark


:

Ok, the next step is to use the query you have built to show all the records

SELECT AP.*
FROM AP
WHERE AP.Vendor_Number & Ap.Invoice_Amount in
(SELECT Q.Vendor_Number & Q.Invoice_Amount FROM TheQuery)

That will probably be slow.

But the real problem is still the Invoice_Number being similar. I think
I need to rethink this problem if you are going to get decent performance.

I'll try to post back later with a better solution.

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


utahbiker7 wrote:
John,
I got the sql code entered as below: It narrows down the results from 400k
records to 41k. I don't believe they are all duplicates but it is hard to
see just what records it is choosing. The results only give me two fields -
the vendor number and invoice amount. Can you tell me how to get the other
fields to show with the results? I need the check_no, check_date and store
number to investigate the results to see if it is indeed a duplicate payment.
I appreciate your help.

SELECT AP.VENDOR_NUMBER, AP.INVOICE_AMOUNT
FROM AP INNER JOIN TMP ON (AP.INVOICE_NUM LIKE TMP.INVOICE_NUM & "*") AND
(AP.INVOICE_AMOUNT=TMP.INVOICE_AMOUNT) AND
(AP.VENDOR_NUMBER=TMP.VENDOR_NUMBER)
WHERE TMP.INVOICE_NUM IS NOT NULL
GROUP BY AP.VENDOR_NUMBER, AP.INVOICE_AMOUNT
HAVING COUNT(*)>1;


:

This might get you started.

SELECT AP.VendorNumber, AP.AmountPaid
FROM [Your Table] as AP
INNER JOIN [Your Table] As Tmp
ON AP.VendorNumber = Tmp.VendorNumber
AND AP.AmountPaid = Tmp.AmountPaid
AND AP.InvoiceNumber LIKE Tmp.InvoiceNumber & "*"
WHERE Tmp.InvoiceNumber is Not Null
GROUP BY AP.VendorNumber, AP.AmountPaid
Having Count(*) > 1

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


utahbiker7 wrote:
I have imported to 2007 Access our accounts payable history. I would like
to analysis the data to determine if we have made duplicate payments to a
vendor. I would like to query the database on vendor number. If within
the vendor number an invoice amount is identical I would like it flagged.
I would also like to narrow those records down to those have similar invoice
numbers. I have found that sometimes the processing of payments is forced
by changing the invoice number - like adding an additional digit to override
the software's controls and thus making a duplicate payment to a vendor.
So, if anyone can help me with a query to identify duplicate payments to a
vendor, I would appreciate it. It has been a long time since I've used
access but I remember at one time it could identify dups... thanks.

 

[utahbiker7]

John,
The program runs fine now but out of 400k line items, it still leaves over
80k lines as selected. I have tried increasing the field length from 5 to 6
and to 7 but it still leaves too many entries. Also, lengthing the field is
surely skipping some invoices because I know some invoice numbers are 5 or
less in length.
Do you have an email address where I could possibly send you a sample of the
data? If you don't want to do that, I understand. You have been more than
helpful and I appreciate all your time.

I believe the way the data is listed also causes some duplicates, too.

Thanks,
Mark

My bad. I started to do this another way and then changed my method in
mid-thought. I forgot to clean up.

Try the following. It is basically an unmatched query as the unmatched query
wizard would create it.

SELECT AP.Vendor_Number, AP.Invoice_Amount, AP.Invoice_Num
FROM AP
WHERE AP.Vendor_Number in
(SELECT Tmp.Vendor_Number
FROM AP as Tmp
GROUP BY Tmp.Vendor_Number, Tmp.Invoice_Amount
, Left(Tmp.Invoice_Num,5)
HAVING Count(*) > 1
AND Tmp.Invoice_amount = AP.invoice_Amount
AND Left(Tmp.Invoice_Num,5) = Left(AP.Invoice_Num,5))



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

John,
I have typed in the query per below. It gives me this error
"You have written a subquery that can return more than one field without
using the EXISTS reserved word in the main query's FROM clause. Revise the
SELECT statement of the subquery to request only one field."
I think I typed in the query correctly but I can't seem to get this
statement above to clear. Would you look at the query for me and see if it
can be changed to clear the above message?

Thanks,
Mark


SELECT AP.Vendor_Number, AP.Invoice_Amount, AP.Invoice_Num
FROM AP
WHERE AP.Vendor_Number in
(SELECT Tmp.Vendor_Number, Tmp.invoice_amount
, Left(Tmp.Invoice_Num,5)
FROM Tmp)
GROUP BY Tmp.Vendor_Number, Tmp.Invoice_Amount
, Left(Tmp.Invoice_Num,5)
HAVING Count(*) > 1
AND Tmp.Invoice_amount = AP.invoice_Amount
AND Left(Tmp.Invoice_Num,5) = Left(AP.Invoice_Num,5)

Just started thinking about solutions that would be close, but not perfect.

SELECT AP.VendorNumber, AP.AmountPaid, AP.InvoiceNumber
FROM [Your Table] as AP
WHERE AP.VendorNumber in
(SELECT Tmp.VendorNumber, Tmp.AmountPaid
, Left(Tmp.InvoiceNumber,5)
FROM [Your Table] as Tmp
GROUP BY Tmp.VendorNumber, Tmp.AmountPaid
, Left(Tmp.InvoiceNumber,5)
HAVING Count(*) > 1
AND Tmp.AmountPaid = AP.AmountPaid
AND Left(Tmp.InvoiceNumber,5) = Left(AP.InvoiceNumber,5)

If I got that built correctly it should return duplicate records based
on Vendor Number, Amount and the first 5 characters of InvoiceNumber
being the same. Of course, you can change the 5 characters to 6 or 7 or
whatever the minimum length of the "normal" Invoice number is.

Hope this works for you. Sorry, it took so long for me to respond, but
I just was not seeing a PERFECT solution and then I figured that this
should let you get close.

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


utahbiker7 wrote:
John,
If the report can give me identical invoice amounts within each vendor, I
can use it to scan for invoice numbers that look similiar. They usually
stand out anyway in that the invoice number will have -1 or -* or some other
character added to the invoice field to make Oracle accept it. This should
be sufficient if we can't programmatically identify those records within each
vendor that have similiar invoice numbers.
Thanks,
Mark


:

Ok, the next step is to use the query you have built to show all the records

SELECT AP.*
FROM AP
WHERE AP.Vendor_Number & Ap.Invoice_Amount in
(SELECT Q.Vendor_Number & Q.Invoice_Amount FROM TheQuery)

That will probably be slow.

But the real problem is still the Invoice_Number being similar. I think
I need to rethink this problem if you are going to get decent performance.

I'll try to post back later with a better solution.

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


utahbiker7 wrote:
John,
I got the sql code entered as below: It narrows down the results from 400k
records to 41k. I don't believe they are all duplicates but it is hard to
see just what records it is choosing. The results only give me two fields -
the vendor number and invoice amount. Can you tell me how to get the other
fields to show with the results? I need the check_no, check_date and store
number to investigate the results to see if it is indeed a duplicate payment.
I appreciate your help.

SELECT AP.VENDOR_NUMBER, AP.INVOICE_AMOUNT
FROM AP INNER JOIN TMP ON (AP.INVOICE_NUM LIKE TMP.INVOICE_NUM & "*") AND
(AP.INVOICE_AMOUNT=TMP.INVOICE_AMOUNT) AND
(AP.VENDOR_NUMBER=TMP.VENDOR_NUMBER)
WHERE TMP.INVOICE_NUM IS NOT NULL
GROUP BY AP.VENDOR_NUMBER, AP.INVOICE_AMOUNT
HAVING COUNT(*)>1;


:

This might get you started.

SELECT AP.VendorNumber, AP.AmountPaid
FROM [Your Table] as AP
INNER JOIN [Your Table] As Tmp
ON AP.VendorNumber = Tmp.VendorNumber
AND AP.AmountPaid = Tmp.AmountPaid
AND AP.InvoiceNumber LIKE Tmp.InvoiceNumber & "*"
WHERE Tmp.InvoiceNumber is Not Null
GROUP BY AP.VendorNumber, AP.AmountPaid
Having Count(*) > 1

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


utahbiker7 wrote:
I have imported to 2007 Access our accounts payable history. I would like
to analysis the data to determine if we have made duplicate payments to a
vendor. I would like to query the database on vendor number. If within
the vendor number an invoice amount is identical I would like it flagged.
I would also like to narrow those records down to those have similar invoice
numbers. I have found that sometimes the processing of payments is forced
by changing the invoice number - like adding an additional digit to override
the software's controls and thus making a duplicate payment to a vendor.
So, if anyone can help me with a query to identify duplicate payments to a
vendor, I would appreciate it. It has been a long time since I've used
access but I remember at one time it could identify dups... thanks.

 

[John Spencer]

I'm sorry but I really can't help you beyond what I've attempted. It sounds
as if you need to hire someone to help out on this one or repost the problem
and perhaps someone else can pick up with a better suggestion.

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

John,
The program runs fine now but out of 400k line items, it still leaves over
80k lines as selected. I have tried increasing the field length from 5 to 6
and to 7 but it still leaves too many entries. Also, lengthing the field is
surely skipping some invoices because I know some invoice numbers are 5 or
less in length.
Do you have an email address where I could possibly send you a sample of the
data? If you don't want to do that, I understand. You have been more than
helpful and I appreciate all your time.

I believe the way the data is listed also causes some duplicates, too.

Thanks,
Mark

My bad. I started to do this another way and then changed my method in
mid-thought. I forgot to clean up.

Try the following. It is basically an unmatched query as the unmatched query
wizard would create it.

SELECT AP.Vendor_Number, AP.Invoice_Amount, AP.Invoice_Num
FROM AP
WHERE AP.Vendor_Number in
(SELECT Tmp.Vendor_Number
FROM AP as Tmp
GROUP BY Tmp.Vendor_Number, Tmp.Invoice_Amount
, Left(Tmp.Invoice_Num,5)
HAVING Count(*) > 1
AND Tmp.Invoice_amount = AP.invoice_Amount
AND Left(Tmp.Invoice_Num,5) = Left(AP.Invoice_Num,5))



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

John,
I have typed in the query per below. It gives me this error
"You have written a subquery that can return more than one field without
using the EXISTS reserved word in the main query's FROM clause. Revise the
SELECT statement of the subquery to request only one field."
I think I typed in the query correctly but I can't seem to get this
statement above to clear. Would you look at the query for me and see if it
can be changed to clear the above message?

Thanks,
Mark


SELECT AP.Vendor_Number, AP.Invoice_Amount, AP.Invoice_Num
FROM AP
WHERE AP.Vendor_Number in
(SELECT Tmp.Vendor_Number, Tmp.invoice_amount
, Left(Tmp.Invoice_Num,5)
FROM Tmp)
GROUP BY Tmp.Vendor_Number, Tmp.Invoice_Amount
, Left(Tmp.Invoice_Num,5)
HAVING Count(*) > 1
AND Tmp.Invoice_amount = AP.invoice_Amount
AND Left(Tmp.Invoice_Num,5) = Left(AP.Invoice_Num,5)

:

Just started thinking about solutions that would be close, but not perfect.

SELECT AP.VendorNumber, AP.AmountPaid, AP.InvoiceNumber
FROM [Your Table] as AP
WHERE AP.VendorNumber in
(SELECT Tmp.VendorNumber, Tmp.AmountPaid
, Left(Tmp.InvoiceNumber,5)
FROM [Your Table] as Tmp
GROUP BY Tmp.VendorNumber, Tmp.AmountPaid
, Left(Tmp.InvoiceNumber,5)
HAVING Count(*) > 1
AND Tmp.AmountPaid = AP.AmountPaid
AND Left(Tmp.InvoiceNumber,5) = Left(AP.InvoiceNumber,5)

If I got that built correctly it should return duplicate records based
on Vendor Number, Amount and the first 5 characters of InvoiceNumber
being the same. Of course, you can change the 5 characters to 6 or 7 or
whatever the minimum length of the "normal" Invoice number is.

Hope this works for you. Sorry, it took so long for me to respond, but
I just was not seeing a PERFECT solution and then I figured that this
should let you get close.

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


utahbiker7 wrote:
John,
If the report can give me identical invoice amounts within each vendor, I
can use it to scan for invoice numbers that look similiar. They usually
stand out anyway in that the invoice number will have -1 or -* or some other
character added to the invoice field to make Oracle accept it. This should
be sufficient if we can't programmatically identify those records within each
vendor that have similiar invoice numbers.
Thanks,
Mark


:

Ok, the next step is to use the query you have built to show all the records

SELECT AP.*
FROM AP
WHERE AP.Vendor_Number & Ap.Invoice_Amount in
(SELECT Q.Vendor_Number & Q.Invoice_Amount FROM TheQuery)

That will probably be slow.

But the real problem is still the Invoice_Number being similar. I think
I need to rethink this problem if you are going to get decent performance.

I'll try to post back later with a better solution.

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


utahbiker7 wrote:
John,
I got the sql code entered as below: It narrows down the results from 400k
records to 41k. I don't believe they are all duplicates but it is hard to
see just what records it is choosing. The results only give me two fields -
the vendor number and invoice amount. Can you tell me how to get the other
fields to show with the results? I need the check_no, check_date and store
number to investigate the results to see if it is indeed a duplicate payment.
I appreciate your help.

SELECT AP.VENDOR_NUMBER, AP.INVOICE_AMOUNT
FROM AP INNER JOIN TMP ON (AP.INVOICE_NUM LIKE TMP.INVOICE_NUM & "*") AND
(AP.INVOICE_AMOUNT=TMP.INVOICE_AMOUNT) AND
(AP.VENDOR_NUMBER=TMP.VENDOR_NUMBER)
WHERE TMP.INVOICE_NUM IS NOT NULL
GROUP BY AP.VENDOR_NUMBER, AP.INVOICE_AMOUNT
HAVING COUNT(*)>1;


:

This might get you started.

SELECT AP.VendorNumber, AP.AmountPaid
FROM [Your Table] as AP
INNER JOIN [Your Table] As Tmp
ON AP.VendorNumber = Tmp.VendorNumber
AND AP.AmountPaid = Tmp.AmountPaid
AND AP.InvoiceNumber LIKE Tmp.InvoiceNumber & "*"
WHERE Tmp.InvoiceNumber is Not Null
GROUP BY AP.VendorNumber, AP.AmountPaid
Having Count(*) > 1

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


utahbiker7 wrote:
I have imported to 2007 Access our accounts payable history. I would like
to analysis the data to determine if we have made duplicate payments to a
vendor. I would like to query the database on vendor number. If within
the vendor number an invoice amount is identical I would like it flagged.
I would also like to narrow those records down to those have similar invoice
numbers. I have found that sometimes the processing of payments is forced
by changing the invoice number - like adding an additional digit to override
the software's controls and thus making a duplicate payment to a vendor.
So, if anyone can help me with a query to identify duplicate payments to a
vendor, I would appreciate it. It has been a long time since I've used
access but I remember at one time it could identify dups... thanks.

 

[utahbiker7]

No problem John - I learned some things with this project. It was just a
project I had on my list that I thought needed some attention this summer.
I truly appreciate your willingness to help.
Mark

I'm sorry but I really can't help you beyond what I've attempted. It sounds
as if you need to hire someone to help out on this one or repost the problem
and perhaps someone else can pick up with a better suggestion.

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

John,
The program runs fine now but out of 400k line items, it still leaves over
80k lines as selected. I have tried increasing the field length from 5 to 6
and to 7 but it still leaves too many entries. Also, lengthing the field is
surely skipping some invoices because I know some invoice numbers are 5 or
less in length.
Do you have an email address where I could possibly send you a sample of the
data? If you don't want to do that, I understand. You have been more than
helpful and I appreciate all your time.

I believe the way the data is listed also causes some duplicates, too.

Thanks,
Mark

My bad. I started to do this another way and then changed my method in
mid-thought. I forgot to clean up.

Try the following. It is basically an unmatched query as the unmatched query
wizard would create it.

SELECT AP.Vendor_Number, AP.Invoice_Amount, AP.Invoice_Num
FROM AP
WHERE AP.Vendor_Number in
(SELECT Tmp.Vendor_Number
FROM AP as Tmp
GROUP BY Tmp.Vendor_Number, Tmp.Invoice_Amount
, Left(Tmp.Invoice_Num,5)
HAVING Count(*) > 1
AND Tmp.Invoice_amount = AP.invoice_Amount
AND Left(Tmp.Invoice_Num,5) = Left(AP.Invoice_Num,5))



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

utahbiker7 wrote:
John,
I have typed in the query per below. It gives me this error
"You have written a subquery that can return more than one field without
using the EXISTS reserved word in the main query's FROM clause. Revise the
SELECT statement of the subquery to request only one field."
I think I typed in the query correctly but I can't seem to get this
statement above to clear. Would you look at the query for me and see if it
can be changed to clear the above message?

Thanks,
Mark


SELECT AP.Vendor_Number, AP.Invoice_Amount, AP.Invoice_Num
FROM AP
WHERE AP.Vendor_Number in
(SELECT Tmp.Vendor_Number, Tmp.invoice_amount
, Left(Tmp.Invoice_Num,5)
FROM Tmp)
GROUP BY Tmp.Vendor_Number, Tmp.Invoice_Amount
, Left(Tmp.Invoice_Num,5)
HAVING Count(*) > 1
AND Tmp.Invoice_amount = AP.invoice_Amount
AND Left(Tmp.Invoice_Num,5) = Left(AP.Invoice_Num,5)

:

Just started thinking about solutions that would be close, but not perfect.

SELECT AP.VendorNumber, AP.AmountPaid, AP.InvoiceNumber
FROM [Your Table] as AP
WHERE AP.VendorNumber in
(SELECT Tmp.VendorNumber, Tmp.AmountPaid
, Left(Tmp.InvoiceNumber,5)
FROM [Your Table] as Tmp
GROUP BY Tmp.VendorNumber, Tmp.AmountPaid
, Left(Tmp.InvoiceNumber,5)
HAVING Count(*) > 1
AND Tmp.AmountPaid = AP.AmountPaid
AND Left(Tmp.InvoiceNumber,5) = Left(AP.InvoiceNumber,5)

If I got that built correctly it should return duplicate records based
on Vendor Number, Amount and the first 5 characters of InvoiceNumber
being the same. Of course, you can change the 5 characters to 6 or 7 or
whatever the minimum length of the "normal" Invoice number is.

Hope this works for you. Sorry, it took so long for me to respond, but
I just was not seeing a PERFECT solution and then I figured that this
should let you get close.

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


utahbiker7 wrote:
John,
If the report can give me identical invoice amounts within each vendor, I
can use it to scan for invoice numbers that look similiar. They usually
stand out anyway in that the invoice number will have -1 or -* or some other
character added to the invoice field to make Oracle accept it. This should
be sufficient if we can't programmatically identify those records within each
vendor that have similiar invoice numbers.
Thanks,
Mark


:

Ok, the next step is to use the query you have built to show all the records

SELECT AP.*
FROM AP
WHERE AP.Vendor_Number & Ap.Invoice_Amount in
(SELECT Q.Vendor_Number & Q.Invoice_Amount FROM TheQuery)

That will probably be slow.

But the real problem is still the Invoice_Number being similar. I think
I need to rethink this problem if you are going to get decent performance.

I'll try to post back later with a better solution.

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


utahbiker7 wrote:
John,
I got the sql code entered as below: It narrows down the results from 400k
records to 41k. I don't believe they are all duplicates but it is hard to
see just what records it is choosing. The results only give me two fields -
the vendor number and invoice amount. Can you tell me how to get the other
fields to show with the results? I need the check_no, check_date and store
number to investigate the results to see if it is indeed a duplicate payment.
I appreciate your help.

SELECT AP.VENDOR_NUMBER, AP.INVOICE_AMOUNT
FROM AP INNER JOIN TMP ON (AP.INVOICE_NUM LIKE TMP.INVOICE_NUM & "*") AND
(AP.INVOICE_AMOUNT=TMP.INVOICE_AMOUNT) AND
(AP.VENDOR_NUMBER=TMP.VENDOR_NUMBER)
WHERE TMP.INVOICE_NUM IS NOT NULL
GROUP BY AP.VENDOR_NUMBER, AP.INVOICE_AMOUNT
HAVING COUNT(*)>1;


:

This might get you started.

SELECT AP.VendorNumber, AP.AmountPaid
FROM [Your Table] as AP
INNER JOIN [Your Table] As Tmp
ON AP.VendorNumber = Tmp.VendorNumber
AND AP.AmountPaid = Tmp.AmountPaid
AND AP.InvoiceNumber LIKE Tmp.InvoiceNumber & "*"
WHERE Tmp.InvoiceNumber is Not Null
GROUP BY AP.VendorNumber, AP.AmountPaid
Having Count(*) > 1

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


utahbiker7 wrote:
I have imported to 2007 Access our accounts payable history. I would like
to analysis the data to determine if we have made duplicate payments to a
vendor. I would like to query the database on vendor number. If within
the vendor number an invoice amount is identical I would like it flagged.
I would also like to narrow those records down to those have similar invoice
numbers. I have found that sometimes the processing of payments is forced
by changing the invoice number - like adding an additional digit to override
the software's controls and thus making a duplicate payment to a vendor.
So, if anyone can help me with a query to identify duplicate payments to a
vendor, I would appreciate it. It has been a long time since I've used
access but I remember at one time it could identify dups... thanks.