Vlookup or similar for Access

[ladyjoa]

Hello



I have two tables in Access: "Product Configuration" which has the configuration of almost all the products manufactured in the plant, and "Completions" which has all the products completed in a timeframe (monthly in this case).

I append monthly reports to the "Completions" table in Access. I want to make sure that all the products in this report are in the "Product Configuration" table, because if not, it will affect my results of monthly completions. Many new products can be added to production, and I will need to update my "Product Configuration" table.

But how do I know if all the products have their product configuration or not? If there are any new product?

All I can think is a VLookup, but since I am working in Access I don't want to be switching. I know there should be a way!

I would appreciate your help!

[June7]

DLookup is the function in Access.

However, consider a FindUnmatched query. The query wizard will help you build.

[rpeare]

If both tables contain the productID (some sort of common unique key) you can check by adding both tables to a query, connect the two tables with an arrow pointing FROM Completions TO ProductConfiguration, add the ID field from both tables, then put IS NULL in the PRODUCT CONFIGURATION criteria, this will show you a list of all ID's in the completions table that do not have a matching item in the ProductConfiguration table, if you're relying on product descriptions to do this I'd suggest you try to find a way to make your process work with a PK descriptions can change over time and if they do you are basically making a bunch of orphan records.

[ladyjoa]

DLookup is the function in Access.

However, consider a FindUnmatched query. The query wizard will help you build.

Thanks for your help!!

[ladyjoa]

Really helpful! Thank you!!