Need to look for duplicate invoice number

[pduncan]

I have an application that imports an Excel Spreadsheet, and processes it. One part of data validation that needs to take place is to check for previously processed invoices.

I have a table that has all of the previously processed invoice numbers and they are all unique.

I need ideas and help with how I would take the invoice number that I just imported ( tblInvoice.InvoiceNo ) and compare it against all previously processed invoices (stored in tblProcessedInvoices.InvoiceNo )

If the invoice has never been processed (i.e. not in the tblProcessedInvoices table) then I will continue with the program. However, if the invoice has been processed (i.e. matches an invoice in the tblProcessedInvoices table) then display a message box, and set a flag (tblInvoice.PreviouslyProcessed – a check box ) so that I can move this to another table (tblNonProcessedInvoices) for later reporting.

Any indeas?


PDUNCAN - MEMPHIS, TN

When I die, I want to die like my grandfather-- who died peacefully in
his sleep. Not screaming like all the passengers in his car.

 

[lespaul]

This query will return a list of invoice number from your import list that do not exist in tblProcessedInvoices. Once you have the record set, you can determine which invoices in tblInvoice need to be processed.

SELECT * FROM tblInvoice WHERE InvoiceNo NOT IN (SELECT InvoiceNo FROM tblProcessedInvoices)

Leslie

 

[pduncan]

Leslie -
no matter what I do, this keeps returning balnk rows

PDUNCAN - MEMPHIS, TN

When I die, I want to die like my grandfather-- who died peacefully in
his sleep. Not screaming like all the passengers in his car.

 

[PHV]

returning balnk rows
Blank rows or no row ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[pduncan]

it returns a blank row


PDUNCAN - MEMPHIS, TN

When I die, I want to die like my grandfather-- who died peacefully in
his sleep. Not screaming like all the passengers in his car.

 

[pduncan]

Also to be sure I would get a retrun, I did make sure that I have at least 1 invoice number that is the same stored in each table

PDUNCAN - MEMPHIS, TN

When I die, I want to die like my grandfather-- who died peacefully in
his sleep. Not screaming like all the passengers in his car.

 

[lespaul]

what is the exact query you are running? please post the SQL.

thanks

Leslie

 

[MichaelRed]

Isn't this hte 'classic' "Unmatched" query, a-la the vernrable query wizzard?





MichaelRed

 

[pduncan]

After going back and reviewing my SQL - I had a field named incorectly. Thanks for the help -

So, this works, but only gives me invoices that have not been processed - how would I use this to show invoices that have been processed (i.e. matches values in table)?

 

[lespaul]

remove the NOT from the query.


Leslie

 

[pduncan]

Thanks Leslie - guess I was overlooking the obvious again - trying to make this harder than it was.

PDUNCAN - MEMPHIS, TN

When I die, I want to die like my grandfather-- who died peacefully in
his sleep. Not screaming like all the passengers in his car.