Update Columns in MS Access Table

Hi All,

I have the following table in Access

InventoryTable
sku  (text)
price (number)
quantity (number)
map  (Yes/No)
map-price (number


sku              price      quantity      map-price
A001      10                2            
A002      5                1            
A003      3                3            

I have a text file that has the SKU & the Map-Price

What steps would I need to take to update the existing table in MS Access with the Map-Price currently sitting in a text file.

Thanks in advance.
eeclintAsked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
For an Access database, you would import the text file into a table (called tblImport for the purpose of example), naming the columns appropriately.

Your update query syntax in Access would be:

UPDATE InventoryTable INNER JOIN tblImport ON InventoryTable.SKU = tblImport.SKU
SET InventoryTable.[Map-Price] = tblImport.[Map-Price]

Open in new window


If your yes/no Map column is used to determine whether this update should take place, you could modify the query like this:

UPDATE InventoryTable INNER JOIN tblImport ON InventoryTable.SKU = tblImport.SKU
SET InventoryTable.[Map-Price] = tblImport.[Map-Price] 
WHERE Map = TRUE

Open in new window

0
 
lwadwellCommented:
1. import the file into a table within access ... call it "map_price_table" for example
2. run the update:
update InventoryTable
Set [map-price] = (select [map-price] from map_price_table where map_price_table.SKU = InventoryTable.SKU)
Where EXISTS (select 1 from map_price_table where map_price_table.SKU = InventoryTable.SKU)

3. Delete the "map_price_table" table.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.