Query Math

[Nathan Plamondon]

I am writing a parts inventory database for my workplace. I have it
mostly working, but the reordering report isn't quite right. I want
the report to show me parts that need to be ordered. The query I'm
using in the report to generate this number is:

UnitsNeeded:
Sum(nz([ReorderLevel])-(nz([UnitsOrdered])-nz([UnitsRemovedFro mInventory])))
with a ">0" filter to limit the report to only parts I need to order.

This is, essentially, subtracting units on hand from the reorder level.
This works great for most of my parts, but for some, it gives me
wildly inaccurate numbers. Sorry for the poor formatting, but I don't
know how else to explain what's happening.

Part Number Reorder
Level SumOfUnitsOrder ed SumOfUnitsRecei ved SumOfUnitsRemov edFromInventory UnitsOnOrder UnitsNeeded UnitsInStock
128G03706 4 3 3 0 0 5 3
128H0922 1 2 2 1 0 4 1
144C1024590 4 8 8 4 0 12 4
25B7507350 30 30 30 0 0 60 30
356D966551 3 3 3 2 0 11 1
388D981256 2 3 3 0 0 3 3

As I said, I am primarily concerned w/ the UnitsNeeded column and these
numbers just don't make sense to me. Any suggestions? I can provide
more information if needed.
TIA

[Geary]

Re: Query Math

I would work with MaxLevel, MinLevel, UnitsOnOrder

[UnitsNeeded]=IIf([UnitsInStock])<=[MinLevel],(([MaxLevel]-[MinLevel])-[UnitsOnOrder]),0)

[Nathan Plamondon]

Re: Query Math

Excellent! I've been fighting this one for a while. Here's how I
tweaked your suggestion to work for me:

UnitsNeeded:
IIf([UnitsOrdered]-[UnitsRemovedFro mInventory]<[ReorderLevel],[ReorderLevel]-([UnitsOrdered]-[UnitsRemovedFro mInventory]),0)

The reason I'm not using [UnitsInStock] and [UnitsOnOrder] is that they
are also queries happening on this report, so Access asks for their
values when running the report. Is there a way to get Access to run
the other queries first and base this one of of those? or am i stuck
using a really convoluted IIf?

[Nathan Plamondon]

Re: Query Math

Also, it seems that this expression is generating duplicate listings on
my report w/ different counts.

[Nathan Plamondon]

Re: Query Math

Also, it seems that this expression is generating duplicate listings on
my report w/ different counts.

[Geary]

Re: Query Math

I can't see why you are using [SumOfUnitsRemov edFromInventory],
[SumOfUnitsOrder ed], [SumOfUnitsRecei ved].
UnitsNeeded should be a calculated field in the query.
You should add [MaxLevel] to the table.


strSQL = "SELECT InventoryId, UnitsInStock, MaxLevel, ReorderLevel, "
strSQL = strSQL & "UnitsOnOrd er, "
strSQL = strSQL & "IIf([UnitsInStock]+[UnitsOnOrder]<= "
strSQL = strSQL & "[ReorderLevel],[MaxLevel]-([UnitsInStock]+ "
strSQL = strSQL & "[UnitsOnOrder]),0) "
strSQL = strSQL & " AS UnitsNeeded FROM tblInventory;"

[Nathan Plamondon]

Re: Query Math

Access apparently doesn't allow subqueries. When I try to use
[UnitsInStock] or [UnitsOnOrder] (which are both calculated in this
query), Access chokes and won't allow me to generate the report. I'm
also rather a newb when it comes to db, so I'm not even sure where I
would type that query or how I would put it into the report.