Question Import from Excel

[PMSToys]

Here's my situation, wonder if anyone can offer any advice?

First of all, I'm a basic user of Excel and an Access newbie. I get databases and the concept of them but that's about where it ends. I run a small online shop and receive a full stock list from my supplier. I upload this straight to the online store to populate it with products.

That's all fine.

However, I have about 3000 or so products as a result and want to change the descriptions on them so I'm not penalised by google for duplicate content.

I thought Access would be able to help me here so I imported the spreadsheet into a blank table and started editting some descriptions.

After which, I get an updated spreadsheet from the supplier, pricing, stock levels that sort of thing. I want to import this into the table but not overwrite my modified descriptions.

I've tried appending it which creates duplicate records. I've tried importing it which overwrites the existing content and I've tried importing it but not importing the description fields but that just blanks out the existing descriptions which is no help!

Can someone point me in the right direction? Basically I want to customise descriptions and still be able to selectively import from the updated Excel worksheets without overwriting the description field in the table.

Cheers

 

[gemma-the-husky]

you probably need to store both the original description and your changed description

then when you load the new data, you can cross-check against the original description, and find the new ones that way.

 

[PMSToys]

Thanks Dave. I had attempted that by renaming the originally imported field to Desc_MOD so that when I re-imported the new spreadsheet, it would not overwrite my modified descriptions but I end up with a duplicate record for the product with the original description in one record under "Description" field and my modified description in a seperate record.

I think my basic problem is how to carry out the subsequent imports. I've googled it to death but all I can ever find is the most basic Excel > Access with no follow up on subsequent imports.

 

[jal]

You might succeed using an UPDATE query. Import the updated pricelist into a separate Access table so that you now have two Access tables. Now this is only going to work if there is some kind of product ID# in each table whereby a given row from one table matches up with a row of the other table. Your update query might look something like this.

UPDATE DescriptionTable as D
INNER JOIN UpdatedPriceList as U
ON D.ProductID = U.ProductID
SET D.Price = U.Price, D.StockLevel = U.StockLevel

Or if you prefer, you can reverse this, so that the Description is written to UpdatedPriceList instead.

UPDATE UpdatedPriceList as U
INNER JOIN DescriptionTable as D
ON D.ProductID = U.ProductID
SET U.Description = D.Description

A third option would be to move the desired values into a third table.

SELECT D.Description, U.Price, P.StockLevel
INTO ThirdTable
FROM DescriptionTable as D
INNER JOIN UpdatedPriceList as U
ON D.ProductID = U.ProductID

But as Gemma suggested, all of this depends on your having some way (such as a ProductID#) to match up rows.