Update query in MS Access with more then one criteria

Question

OK need some help on this one, I have the following: I am using MS access 2016
I have stores: A,B,C,D
All four stores have the same inventory type for fruit they have lets say: apples, oranges, plums and bananas
and Drink they have coke, sprite, Fanta, Pepsi
What i would like to do is:

1. have an inventory for each store with each item and QTY
2. I want to use a form or table that will subtract the QTY of apple and coke that was purchased from store A or oranges and Sprite from store B

I get lost cause i am not sure how to write a query or SQL the will take the store and the fruit and the drink into consideration so it know how much to take away and from what.

Any help would be grerat.

Answer 1

What do your tables look like? In my head I see a Stores table that holds the information for the Stores. I see inventory or warehouse table. This could be an overall inventory for the chain of stores. You could also another table that shows how much inventory each store has. Then a couple of queries that join these together. This will get you your 1 point. for Subtracting you would capture the qty change and then write an update query.

Stores Table.
StoreID , StoreName, StoreAddress, StorePhoneNumber, InActiveDate, InActiveBy,

Inventory Table
Inventoryid , InventoryName, InventoryDescription, TotalQty , AdditionalQty , InventoryDate, InventoriedBy,InActiveDate, InActiveBy
StoreInventory
SI_ID, StoreID , InventoryID, StoreQty, AdditionalQty , InventoryDate, InventoriedBy,InActiveDate, InActiveBy

query or view
SELECT StoreInventory.StoreID, StoreInventory.InventoryID, StoreQty, StoreName, InventoryName, InventoryDescription
From StoreInventory inner join StoresTable on StoreInventory.Storeid = StoresTable.StoreID
Inner Join Inventorytable. on StoresTable.InventoryID = Inventorytable.inventoryid