J
Josie Lions
I am trying to set up a database for a small clothing company. I have a form that has customer information as well as their orders based on 2 tables: customer information & order details. I am also trying to do inventory in the same database. To complete this, I have created an additional table of Purchased Inventory which identifies my supplier and the inventory purchased from them. I used a query to total the amount of items sold based on my Order Details table. The items are separated by a product type so I can see the number of shirts sold, number of jeans sold, etc. I also used a query to total the amount of inventory I purchased that also separates by type. Now I am trying to do a third query that would essentially subtract the number of items sold from the number of items purchased thus giving me the number of items currently in stock. But the catch is I want it to separate the products so number of shirts in stock, number of jeans in stock, etc.
I have tried to do this several ways but the closest I can get it is showing me the correct number of units from inventory, but the number sold (order details) for each type of product, not just one. For example 100 shirts from inventory, 3 sold (correct #); 100 shirts from inventory 5 sold (5 is really # of jeans), 100 shirts from inventory 45 sold (45 is # of socks), etc. How can I get this corrected?
Hopefully this all makes sense. I haven't really entered any items in the Order Details table or the Inventory Purchases table because I want to make sure everything is correct first.
I have tried to do this several ways but the closest I can get it is showing me the correct number of units from inventory, but the number sold (order details) for each type of product, not just one. For example 100 shirts from inventory, 3 sold (correct #); 100 shirts from inventory 5 sold (5 is really # of jeans), 100 shirts from inventory 45 sold (45 is # of socks), etc. How can I get this corrected?
Hopefully this all makes sense. I haven't really entered any items in the Order Details table or the Inventory Purchases table because I want to make sure everything is correct first.