Updating vendor pricing from Excel

[sstiebinger]

Not sure if this is best asked in the import section, or the query section, but thought I'd try here.

I will have a table in Access that has fields ID(Autonum), PartNum(Text), Price(Currency), Legacy(Boolean)
I have pricing data in excel in the format of 2 columns, column A has the part number, and column B has the price.
When I get an updated price list from my vendors, I want to update my access table.

I'm assuming that step one will be importing the excel file into a temporary table, but if there's a better way, I'm all ears.

I want to perform the following updates to compare the new price list to the existing table:
Where partnum matches, update price from temp table to main table (Updated price)
Where partnum is in temp table, but not main table, add partnum and price to main table (New Part)


Where partnum is in main table, but not temp table, change Legacy to "True" (Discontinued part)
Clean up (Remove temp table)

Can anyone point me in the right direction, or post some code snippets I might modify to accomplish this?

Thanks in advance.

[June7]

Can you set link to the Excel? If you can, then just build UPDATE queries.

Do you have dependent records that rely on link to the price table to retrieve price? Do you want to all these dependent records to work with the updated price info? Or should these historical records retain the old price?

[sstiebinger]

This database is used as a job quotation tool so quotes in the system will reference products that may no longer exist...
I suppose I could just store the current data in a new field when creating the proposal... I'll have to think through the ramifications of this...

Basically, during the sales stage the quotations would get the latest price for the products.
Once a proposal is accepted by the customer, the quotation would then be stored with the pricing that was current at the time of sale.

This is a multi-user application, and the excel file is on a shared network drive, so for performance reasons I'd also rather not have to worry about linked file bottlenecks.
Also, if the file is linked, doesn't it lock the file from editing while the database is open? That's a non-starter for this application as we'll need to update that file at regular intervals (but we're satisfied with monthly updates to the quotation tool.)

[sstiebinger]

I think I may have misunderstood your question...

for the once a month update process, yes, I can just link to the excel file then unlink the file when I'm done.
For the second part... there will be "Live" quotations getting current data, and "Stored" quotations with historical data.

[sstiebinger]

so here's what I'm thinking based on your nudge... does this sound viable?

Link to excel sheet as tblTempExcel

UPDATE tblPricing
INNER JOIN tblTempExcel ON [tblPricing].[Partnum] = [tblTempExcel].[F1]
SET [tblPricing].[Price] = [tblTempExcel].[f2]

UPDATE tblPricing
LEFT JOIN tblTempExcel ON [tblPricing].[Partnum] = [tblTempExcel].[F1]
SET [tblPricing].[Legacy] = True
WHERE [tblTempExcel].[F1] IS NULL

UPDATE tblPricing
RIGHT JOIN tblTempExcel ON [tblPricing].[Partnum] = [tblTempExcel].[F1]
SET [tblPricing].[Partnum] = [tblTempExcel].[F1], [tblPricing].[Price] = [tblTempExcel].[F2]
WHERE [tblPricing].[Partnum] IS NULL

Then drop the linked table...

Does that sound about right?

[sstiebinger]

Just tested and this works perfectly.... thanks for the nudge in the right direction.