question

Keith-3520 avatar image
0 Votes"
Keith-3520 asked ·

Update query in MS Access with more then one criteria

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.



office-access-dev
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Robert-6510 avatar image
0 Votes"
Robert-6510 answered ·

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

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.