Calculating inventory w/ blank field results

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

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

dirtrhoads wrote:
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
http://www.accessmonster.com/Uwe/For...ccess/201005/1

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



"KenSheridan via AccessMonster.com" wrote:

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

dirtrhoads wrote:
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
http://www.accessmonster.com/Uwe/For...ccess/201005/1

.

On Wed, 12 May 2010 17:20:01 -0700, dirtrhoads
wrote:

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.

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


--

John W. Vinson [MVP]

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

dirtrhoads wrote:
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

--
Message posted via http://www.accessmonster.com

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!!

"KenSheridan via AccessMonster.com" wrote:

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

dirtrhoads wrote:
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

--
Message posted via http://www.accessmonster.com

.

On May 13, 8:30*am, dirtrhoads
wrote:
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!!

"KenSheridan via AccessMonster.com" wrote:
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

dirtrhoads wrote:
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

--
Message posted viahttp://www.accessmonster.com

.

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