Help with Query please

[fordtran]

I have an Access 2000 database and wish to set up a query which will do the following :
I have an ITEMS table which contains the following fields : ID, ITEMNAME, STARTINGSTOCK, MINIMUMSTOCK
A second table contains my purchases of this item. The table would be : ITEMSPURCHASED with fields : PURCHASEID, ITEMID (same as in ITEMS table), QUANTITYBOUGHT.
A third table contains my issued items. The table would be : ITEMSISSUED with fields : ISSUEID, ITEMID (as in ITEMS table, QUANTITYISSUED.
I now wish to make a query to find out how many items are in stock and more specifically the items which are below minimum stock levels like in :

Look at what the starting stock is, add the sum of the purchases, subtract the sum of the stock issued, compare the present quantity in stock with the minimum stock level and show me the items below minimum stock levels in order of deficiency i.o.w. starting the schedule with the highest stock deficiency
Sorry for the tall order but this is beyond my knowlegde/ability and you guys have helped me everytime in the past
Thanks


fordtran

 

[lespaul]

I can't tell if you've already set this all up or if you're asking for help setting this all up. You say you HAVE an Items table but the ItemsPurchased table "would be".... Do you actually HAVE an ItemsPurchased table?

Additionally, if you search the fora you can find multiple threads that talk about how to set up an inventory system. Most of them agree that there should be a single transaction table that indicates whether the entry is a purchase, return, inventory addition, etc., not multiple tables for each transaction type.

HTH


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins