I need help on creating a query (single so I can use this as a basis for a form) That will look for changes in my product table compared to the latest table created from my supplier updates (Imported from Excell files).

What I need is for the query to add any items in the Update Table that DONT already exist in the current product list.

This seems rather easy yes?? Except that my supplier lists are lal in different formats and Column lables (eg: one is "Tier1" for pricing, and others may be "price" etc). My actual product table uses "price"... Here is the basic layout...

Product (Table) Fields that matter


Exaple Update (Table)

Tier1 (Price)
Code (ProductCode)
Supplier (SupplierName)
Features (ProductName)

* Words encased in () reveal the matching fields in the Product Table

Now, WHat I need, is for the query to match the codes with ProductCodes, and Supplier with SupplierName in the existing table (therefore all existing products will show price changes in Tier1. I also need it to append the codes NOT matched to existing products, and add the "Code" to the relevant fields to the existing fields (eg Code to ProductCode, Tier1 to Price, Features to ProductName, and Supplier to SupplierName).

Basically, once this is done, the Products Table should now hold ALL Existing and New Products from the Update Table.

I have been able to create queries that find the Matched and UnMatched Records, but one uses the Products Table to find the Matches, and the Updates Table to find the UnMatched... How do I get these to combine into a single query?

I want the Final query to use ALL fields in both tables, so that when used as the basis for the form, if the price is different to the Tier1 I get a message telling me it has changed, and have the prog update it. This bit I have done, but without the "NEW" products included.

In Regards to the "New" Products, I need the form to show these, and append the fields in the Product Table using Form_Current().

EG: If Not IsNull(Me.Code) ANd ISNull(Me.ProductCode) Then Me.ProductCode = Me.Code .... Same for reversal (EG: ProductCode to Code). Will also add a msgbox stating the changes.

In Brief I need a query that will...
1. check for and add new products to its list
2. Allow the Form its based on to check and change price differences
3. Allow the form it is based on to mark Products that exist in the Products Table BUT NOT in the Update Table to be marked as "Discontinued"
4. And Then Mark Products LIsted in the Updates Table BUT NOT in the Products Table as "NEW"

Can anyone help here??