Update table after comparing to others

[simplyJ]

I have two tables in MS Access. PRODUCTS is a list of items that our business carries. INVENTORY is a summary of things that we have or don't have in stock right now.

Is it possible to write a query that will compare the items in INVENTORY to PRODUCTS and when a match is found, the stock field in PRODUCTS will be updated to the value of the stock field in INVENTORY.

example of tables and fields:

PRODUCTS
name (primary)
stock
price
etc..

INVENTORY
name (primary)
stock

If I cannot write a SQL query for this is it possible to make a module do this in Access?

 

[swampBoogie]

Why store the same value in two places?

Update products,inventory
set products.stock = inventory.stock
where products.name = inventory.name

 

[Isadore]

simply: My first suggestion is to elminate completely your Inventory table, since both Name and Stock appear in your principal table Products. If your stock number drops to "0" in Products, then you know you're inventory has been depleted. I don't see any reason for duplicating this data in a second table. If your inventory table had a 1 to many relationship with the Products table a different argument would apply; but since these two tables are in a 1 to 1 relationship, I'd get rid of the Inventory table and maintain stock numbers as a field in your products column.

To differentiate whether a product is in stock versus not in stock you could look to the "0" level, or, alternatively, you could add a "Yes/No" checkbox that is checked if stock > 0; otherwise it is not checked (and lock it out so the user has no control over it).

 

[simplyJ]

I should have included more info. My fault.

I am just trying to prove that this stystem will work right now. The database is not in production. Inventory right now is kept in excel spreadsheets.

I have built the database with all of the products, but we still do inventory in Excel. I was planning on importing the excel spreadsheet into a table in the database so it would be easier to work with. [I am being lazy...I don't want to key in all of the products inventory by hand.]

After I get the two tables I don't know where to go from there.

 

[Isadore]

simply: Just run a DLookUp or DCount and find out what's going on in your Inventory table. That should work.

 

[simplyJ]

Thanks!

I think I have this thing working.

 

[Isadore]

Good. Not sure if you're using a DLookUp (use a variant here in case you bring back a NULL) or DCount. I use a DCount often just to check if anything is there. Best of luck. We'll be seeing ya around the forum.

 

[Isadore]

simply: SwampBoogie gave you the perfect code lines for updating, if you had to do it this way, that'd be the way I'd do it.