How do you do a vlookup in Access

Jacquilov

New member
Local time
Today, 00:13
Joined
Mar 21, 2010
Messages
4
How would I do a vlookup in access to identify missings transactions from 2 tables.?
I created the Master Table and I want to extract only the transactions not listed in the Master Table from the Query Table.

Master table has 98 records Query Table has 250 records
 
DLookup will not help in this case. You want to use something called the UNMATCHED query wizard.
 
If this is just a one-off data clensing task, create a query with both tables, join the like fields and make it a one-to-many join (show all records in table X and matching records in table Y). Add your joining fields to the query and in the field from the many table (Y in this case) put "null" as the criteria. see attachment 'find_missing.jpg'

If you're wanting to do this programatically, create a SQL query to look for the record in question and test the RecordCount afterwards. If it's zero, it wasn't there.
 

Attachments

  • find_missing.jpg
    find_missing.jpg
    41.9 KB · Views: 292
If this is just a one-off data clensing task, create a query with both tables, join the like fields and make it a one-to-many join (show all records in table X and matching records in table Y). Add your joining fields to the query and in the field from the many table (Y in this case) put "null" as the criteria. see attachment 'find_missing.jpg'

If you're wanting to do this programatically, create a SQL query to look for the record in question and test the RecordCount afterwards. If it's zero, it wasn't there.

Exactly, which is what the Unmatched Query Wizard builds. Thanks for providing the written explanation and a screenshot. I think you captured what I couldn't figure out how to write. :)
 
hehe! I'm still using Access 97 (although that screen shot was in Access 2003... have it installed at home but use '97 at work!) so any thing I suggest is going to be 'old school' methods :)

Cheers, Nige

BTW, Bob, I'm new to this forum and have been reading lots of threads and see your name poping up everywhere with ideas and solutions... thanks, I've learnt of heaps of things I'll keep in the back of my head to solve a problem one day!
 

Users who are viewing this thread

Back
Top Bottom