Update query in MS Access with more then one criteria

Keith 1 Reputation point

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.

Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
774 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Robert 1 Reputation point

    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
    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

    0 comments No comments