Access 2000 - Importing supplier price list into a already existing table ?

[Johnny Dee]

Hello, I am a novice Access user so sorry if this is a simple question.

I have a Database which I have a price list table from each supplier. It
contains basic information of their products, i.e.; Cost price, Recommended
retail price, Product code, Supplier Code etc...

My question is:

How do I goo about importing the NEW price list from this supplier into the
current table so that it "Updates" existing product coded prices inn the
table and add the remainder that's not already in the table?

Any direction appreciated.

Thank you,

John Dee.

 

[Larry Linson]

In what form is your "price list table"? Is it an Access table? A comma
delimited text file? A fixed-field text? That would make a big difference.

Larry Linson
Microsoft Access MVP

 

[Johnny Dee]

Hi,
My table, which has the combined Suppliers Pricelist in is an Access Table.
The New Supplier pricelist updates are usually in an excel format.

Thanks,

John.

 

[John Vinson]

How do I goo about importing the NEW price list from this supplier into the
current table so that it "Updates" existing product coded prices inn the
table and add the remainder that's not already in the table?

You'll usually need TWO queries: an Update query to update the
existing records, and an Append query to add new ones. I see
elsethread that the data is in Excel; you can use File... Get External
Data... Link to link to the spreadsheet, getting what works very like
an Access table for these purposes.

Create a Query joining the new price list to the old one by
ProductCode, using the supplierID as a criterion if there are multiple
suppliers; change the query to an Update query; and on the Update To
line under Pricelist.Price put

[NewPrices].[Price]

You do need the brackets - use the table name of your linked
spreadsheet and the fieldname (column head) in that sheet.

For the Append query, be sure that the ProductCode has a unique Index;
just create an Append query based on the linked spreadsheet appending
the ProductCode, Price, and any other information (description, etc.)
You'll get a warning message "xxx records were not appended due to key
violation" - worry not, that's just the existing records that you've
already updated.