By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,720 Members | 2,244 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,720 IT Pros & Developers. It's quick & easy.

Query Math

P: n/a
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([UnitsRemovedFromInventory])))
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 SumOfUnitsOrdered SumOfUnitsReceived SumOfUnitsRemovedFromInventory 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

Apr 19 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
I would work with MaxLevel, MinLevel, UnitsOnOrder

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

Apr 19 '06 #2

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

UnitsNeeded:
IIf([UnitsOrdered]-[UnitsRemovedFromInventory]<[ReorderLevel],[ReorderLevel]-([UnitsOrdered]-[UnitsRemovedFromInventory]),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?

Apr 19 '06 #3

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

Apr 19 '06 #4

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

Apr 19 '06 #5

P: n/a
I can't see why you are using [SumOfUnitsRemovedFromInventory],
[SumOfUnitsOrdered], [SumOfUnitsReceived].
UnitsNeeded should be a calculated field in the query.
You should add [MaxLevel] to the table.
strSQL = "SELECT InventoryId, UnitsInStock, MaxLevel, ReorderLevel, "
strSQL = strSQL & "UnitsOnOrder, "
strSQL = strSQL & "IIf([UnitsInStock]+[UnitsOnOrder]<= "
strSQL = strSQL & "[ReorderLevel],[MaxLevel]-([UnitsInStock]+ "
strSQL = strSQL & "[UnitsOnOrder]),0) "
strSQL = strSQL & " AS UnitsNeeded FROM tblInventory;"

Apr 20 '06 #6

P: n/a
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.

Apr 20 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.