Current Inventory Level Calculation

[leadadmin]

I have an Inventory table which contains partno, inventorycount, and countdate from the last physical inventory taken. I also have a Purchases table, which contains partno (same as in Inventory table), purchaseqty, and purchasedate.

I want to create a report that shows my current inventory level for all parts, after purchases made after Inventory][countdate]. I made a query called Current Inventory, linking the two tables by partno. The join properties are Use all records from Inventory and only those from Purchases where joined fields are equal. From Inventory I took partno and inventorycount. From Purchases I took purchaseqty and purchasedate>[Inventory][countdate].

The query only returns records for parts where purchases were made. Several partnos had no purchases, so they do not appear in the query results nor, hence, in the report. I get the same results no matter which join property I select in the query.

I know this should be simple but I can't seem to get it. Thanks for your help.

 

[SuicidED]

Hi
Base a query on your purchase table first using your criteria
SELECT PURCHASES.partno, PURCHASES.purchaseqty, PURCHASES.purchasedate
FROM PURCHASES INNER JOIN Inventory ON PURCHASES.PARTNO = Inventory.PARTNO
WHERE (((PURCHASES.purchasedate)>[Inventory]![countdate]));
then do a second query using the inventory table and your first query
SELECT Inventory.PARTNO, Inventory.inventorycount, Query1.purchaseqty
FROM INV LEFT JOIN Query1 ON INV.PARTNO = Query1.PARTNO;
This should give you what you want

 

[KenReay]

Hi

Should the first query in the example given not be an agregate query to sum Purchases by part number, presumably it is possible to have more than one purchase for a given part

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[SuicidED]

Whoops yes it should
Cheers Eddie

 

[leadadmin]

Wonderful! I did exactly as you outlined and it works beautifully. Thank you so much.

Just one minor snag: if there are no Purchase records (and, hence, no purchase qty) for a part number, when I subtract purchases from counted qty to get my current inventory level by using =[Qty On Hand]-Sum([Inventory Balance]!Qty)in the report, that field is blank.

How can I always get a final inventory level?

 

[KenReay]

Hi

try

=[Qty On Hand]-Sum(Nz([Inventory Balance]!Qty,0))

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[leadadmin]

That did the trick! Thank you very much.