Update Price based on multiple column

[JustinC]

Hi all,



I am using a closest example to illustrate my problem.

Assuming I have a main table(Supplier) which I would like to update the Price column.

Name	Product1	Product2	Product3	Product4	Price
Jack	Book	File	Pen		15
John	Stationary	Paper	Eraser	Staple	10
Johnson	Toys	Box			8
Alice	Ruler	Keychain			7
Mary	Pencil	Pencil Box			4
May	Stationary	Paper			5
Tim	Pencil	File			2

^Supplier table


I have another table which I would like to use to update the Supplier table.

Product1	Product2	Product3	Product4	Price
Book	File	Pen		16
Stationary	Paper	Eraser	Staple	11
Toys	Box			9
Ruler	Keychain			8
Pencil	Pencil Box			4

^Price table
How can I join the two table using the 4 columns(Product1,Product2,Product3,Product4) such that I can update the "Price" column in the Supplier table based on the "Price" column from the Price table.

I would appreciate any help and guidance that is offered to me.

Thanks!

[ItsMe]

I see two issues. You state you have a main table Supplier that you want to join to your Supplier table. You have two supplier tables?

Also, you should have other tables that support the supplier table rather than "Committing to Spreadsheet" the different products. In other words, use a products table (that has a Foreign Key field for SupplierID) to keep track of products and join this table to a single Supplier table on the Foreign/Primary keys.

[JustinC]

I see two issues. You state you have a main table Supplier that you want to join to your Supplier table. You have two supplier tables?

Also, you should have other tables that support the supplier table rather than "Committing to Spreadsheet" the different products. In other words, use a products table (that has a Foreign Key field for SupplierID) to keep track of products and join this table to a single Supplier table on the Foreign/Primary keys.

Hi, Sorry for the confusion there is only 1 Supplier table. Is there a work around based on what I mention in the previous post? Because the data I am going to update is generated from another access Program which I do not have access to. I am processing the data generated from the access Program which I do not have access to. Thanks!

[John_G]

I see a huge problem here (more than one, actually).

Your data is not properly normalized.

What happens if someone needs more than 4 products?
What happens if more than one supplier can supply the same product?

Your second table would need every possible combination of 1, 2, 3 or 4 products, and in the SAME ORDER as they appear in the supplier table.

I asssume "Price" is the total of the prices for all the products?

What you need is a price table, that contains (at least) Supplier, Product and Price, and go from there.

Can you provide some more detail as to what your are trying to do here?

HTH

John