Calculating inventory w/ blank field results

[dirtrhoads]

I created an inventory query in Access 2007 much like the Northwind 2007
query, but the fields that are supposed to calculate a total are returning
nothing at all.

My SQL statements is:

SELECT Products.Product_Name, [Quantity Produced]-[Quantity Shipped] AS [Qty
On Hand], Nz([Quantity Shipped],0) AS [Qty Shipped], Nz([Quantity
Allocated],0) AS [Qty Allocated], Nz([Quantity on Backorder],0) AS [Qty on
Backorder], ([Quantity Produced]-[Quantity Shipped]-[Quantity
Allocated]-[Quantity on Backorder]) AS [Qty Available], Nz([Quantity
Produced],0) AS [Qty Produced]
FROM (((Products LEFT JOIN [Product Allocated at Belmont] ON
Products.Product_ID=[Product Allocated at Belmont].Product) LEFT JOIN
[Product Backorder at Belmont] ON Products.Product_ID=[Product Backorder at
Belmont].Product) LEFT JOIN [Product Inbound at Belmont] ON
Products.Product_ID=[Product Inbound at Belmont].Product) LEFT JOIN [Product
Shipped from Belmont] ON Products.Product_ID=[Product Shipped from
Belmont].Product;


If my fields Qty Allocated, Qty On Backorder, Qty Shipped, and Qty Produced
are 0 then Qty on Hand and Qty Available return nothing at all. However, if
there is a value > 0 in any of the fields, then the calculation will
calculate properly.

I compared my statement to the statement in Northwind and don't see much of
difference. Can someone please educate me a little further?

Thank you,
Amy

 

[dirtrhoads]

Truthfully, your 2nd option really confused me... I went with the first
option just to test it and that doesn't work appropriately... IE:

Quantity Available = produced - shipped - allocated - backorder

If both produced and shipped are > 0 but allocated and backorder are 0, the
Nz function returns 0 in Quantity Available, which is not correct... it
should return produced - shipped at the very least.

I'm not sure if I'm misunderstanding option 2, but this wouldn't work for my
inventory because I need to show 0 if there is a balance of 0. That's the
reason for the Nz function.

If you have any other suggestions, they would be greatly appreciated.

Thank you,
Amy

The fact that you are applying the Nz function to the columns when returning
them individually suggests that Nulls are allowed. In the expressions which
subtract the values, however, you are not applying the Nz function to each
column. As Null propagates in arithmetic expressions the result will be Null
if any one column is Null.

You have two alternative solutions.

1. Apply the Nz function to each column in the expressions to return zero in
the case of a column being Null.

2. My preferred solution, however, would be to disallow Nulls in the
columns in the first place. For this you'd first need to execute update
queries on each relevant table to update the value of each column to zero
WHERE the column IS NULL. Then set the Required value of each column to True
to disallow Nulls. Finally set the DefaultValue property of each column to
zero. You would then no longer need to call the Nz function in the query as
no column would be Null. More fundamentally the semantic ambiguity of Null
is then avoided.

Ken Sheridan
Stafford, England

I created an inventory query in Access 2007 much like the Northwind 2007
query, but the fields that are supposed to calculate a total are returning
nothing at all.

My SQL statements is:

SELECT Products.Product_Name, [Quantity Produced]-[Quantity Shipped] AS [Qty
On Hand], Nz([Quantity Shipped],0) AS [Qty Shipped], Nz([Quantity
Allocated],0) AS [Qty Allocated], Nz([Quantity on Backorder],0) AS [Qty on
Backorder], ([Quantity Produced]-[Quantity Shipped]-[Quantity
Allocated]-[Quantity on Backorder]) AS [Qty Available], Nz([Quantity
Produced],0) AS [Qty Produced]
FROM (((Products LEFT JOIN [Product Allocated at Belmont] ON
Products.Product_ID=[Product Allocated at Belmont].Product) LEFT JOIN
[Product Backorder at Belmont] ON Products.Product_ID=[Product Backorder at
Belmont].Product) LEFT JOIN [Product Inbound at Belmont] ON
Products.Product_ID=[Product Inbound at Belmont].Product) LEFT JOIN [Product
Shipped from Belmont] ON Products.Product_ID=[Product Shipped from
Belmont].Product;

If my fields Qty Allocated, Qty On Backorder, Qty Shipped, and Qty Produced
are 0 then Qty on Hand and Qty Available return nothing at all. However, if
there is a value > 0 in any of the fields, then the calculation will
calculate properly.

I compared my statement to the statement in Northwind and don't see much of
difference. Can someone please educate me a little further?

Thank you,
Amy

--
Message posted via AccessMonster.com


.

 

[John W. Vinson]

Truthfully, your 2nd option really confused me... I went with the first
option just to test it and that doesn't work appropriately... IE:

Quantity Available = produced - shipped - allocated - backorder

If both produced and shipped are > 0 but allocated and backorder are 0, the
Nz function returns 0 in Quantity Available, which is not correct... it
should return produced - shipped at the very least.

How are you using NZ? What Ken is suggesting is

QuantityAvailable: NZ([Produced]) - NZ([Shipped]) - NZ([Allocated]) -
NZ([Backorder])

in a query, or leave off the brackets and use variable names rather than
fieldnames in VBA code. My guess is that you instead used

NZ( produced - shipped - allocated - backorder)

which will calculate a NULL value for the expression if any individual field
is null, and then turn that NULL into zero (which is in fact NOT what you
want).

I'm not sure if I'm misunderstanding option 2, but this wouldn't work for my
inventory because I need to show 0 if there is a balance of 0. That's the
reason for the Nz function.

Ummm...

35 - 30 - 5 - 0 is in fact zero. So is 0 - 0 - 0 - 0. The balance will be
zero if the numbers sum to zero, which they will if they are all zero.

NULL means "this value is unknown, unspecified, undefined". If the Shipped
value is NULL, that would best be interpreted as "We might have shipped some,
but I have no idea how many". If you know, in fact, that zero items were
shipped, then you really should - as Ken suggests! - store a numeric 0 in that
field, and it will calculate correctly. The only thing NZ() does is to force
the (possibly invalid!!!) assumption that a NULL field is in fact a zero.

 

[dirtrhoads]

I used the first suggestion: QuantityAvailable: NZ([Produced]) -
NZ([Shipped]) - NZ([Allocated]) -NZ([Backorder])

This worked out perfect!! Thank you both so much! Now I understand the Null
value a little more which will be extremely helpful going forward with this
database.

Have a great day!!

The Nz function must be applied to each operand in the expression, not the
result of the expression:

Quantity Available = Nz(produced,0) - Nz(shipped,0) - Nz(allocated,0) - Nz
(backorder,0)

When you say "If both produced and shipped are > 0 but allocated and
backorder are 0, the Nz function returns 0 in Quantity Available" I can only
assume you mean "allocated and backorder are Null" as:

100 - 75 - 0 - 0 = 25

whereas:

100 - 75 - Null - Null = Null

because of the propagation of Null in arithmetical expressions. Applying the
NZ function to the result of this:

Nz(100 - 75 - Null - Null,0) = 0

On the other hand:

Nz(100,0) - Nz(75,0) - Nz(Null,0) - Nz(Null,0) = 25

Ken Sheridan
Stafford, England

Truthfully, your 2nd option really confused me... I went with the first
option just to test it and that doesn't work appropriately... IE:

Quantity Available = produced - shipped - allocated - backorder

If both produced and shipped are > 0 but allocated and backorder are 0, the
Nz function returns 0 in Quantity Available, which is not correct... it
should return produced - shipped at the very least.

I'm not sure if I'm misunderstanding option 2, but this wouldn't work for my
inventory because I need to show 0 if there is a balance of 0. That's the
reason for the Nz function.

If you have any other suggestions, they would be greatly appreciated.

Thank you,
Amy

The fact that you are applying the Nz function to the columns when returning
them individually suggests that Nulls are allowed. In the expressions which

[quoted text clipped - 49 lines]

Thank you,
Amy

 

[James A. Fortune]

I used the first suggestion: QuantityAvailable: NZ([Produced]) -
NZ([Shipped]) - NZ([Allocated]) -NZ([Backorder])

This worked out perfect!! Thank you both so much! Now I understand the Null
value a little more which will be extremely helpful going forward with this
database.

Have a great day!!

The Nz function must be applied to each operand in the expression, not the
result of the expression:

Quantity Available = Nz(produced,0) - Nz(shipped,0) - Nz(allocated,0)- Nz
(backorder,0)

When you say "If both produced and shipped are > 0 but allocated and
backorder are 0, the Nz function returns 0 in Quantity Available"  I can only
assume you mean "allocated and backorder are Null" as:

100  - 75 - 0 - 0 = 25

100  - 75 - Null - Null = Null

because of the propagation of Null in arithmetical expressions.  Applying the
NZ function to the result of this:

Nz(100  - 75 - Null - Null,0) = 0

On the other hand:

Nz(100,0) - Nz(75,0) - Nz(Null,0) - Nz(Null,0) = 25

Ken Sheridan
Stafford, England

Truthfully, your 2nd option really confused me... I went with the first
option just to test it and that doesn't work appropriately... IE:
Quantity Available = produced - shipped - allocated - backorder
If both produced and shipped are > 0 but allocated and backorder are 0, the
Nz function returns 0 in Quantity Available, which is not correct... it
should return produced - shipped at the very least.
I'm not sure if I'm misunderstanding option 2, but this wouldn't work for my
inventory because I need to show 0 if there is a balance of 0. That's the
reason for the Nz function.
If you have any other suggestions, they would be greatly appreciated.
Thank you,
Amy
The fact that you are applying the Nz function to the columns when returning
them individually suggests that Nulls are allowed.  In the expressions which
[quoted text clipped - 49 lines]
Thank you,
Amy

.

I think that when the Nz() function is used in a query (versus within
VBA), that Ken's example of explicitly putting in the default value of
0 is more robust. The expression service used by queries seems to use
a different version of Nz() than VBA that doesn't always know that the
default replacement value is 0. Maybe the problem was fixed in Access
2007. Another way is to use: IIF(X IS NULL, 0, X)

James A. Fortune
(e-mail address removed)