• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 391
  • Last Modified:

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.
0
eeclint
Asked:
eeclint
1 Solution
 
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
 
mbizupCommented:
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now