A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Calculating inventory w/ blank field results



 
 
Thread Tools Display Modes
  #1  
Old May 12th, 2010, 09:56 PM posted to microsoft.public.access
dirtrhoads
external usenet poster
 
Posts: 15
Default 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
  #2  
Old May 12th, 2010, 11:37 PM posted to microsoft.public.access
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Calculating inventory w/ blank field results

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

  #3  
Old May 13th, 2010, 01:20 AM posted to microsoft.public.access
dirtrhoads
external usenet poster
 
Posts: 15
Default Calculating inventory w/ blank field results

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

.

  #4  
Old May 13th, 2010, 05:55 AM posted to microsoft.public.access
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Calculating inventory w/ blank field results

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]
  #5  
Old May 13th, 2010, 10:48 AM posted to microsoft.public.access
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Calculating inventory w/ blank field results

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

  #6  
Old May 13th, 2010, 01:30 PM posted to microsoft.public.access
dirtrhoads
external usenet poster
 
Posts: 15
Default Calculating inventory w/ blank field results

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

.

  #7  
Old May 27th, 2010, 10:45 PM posted to microsoft.public.access
James A. Fortune
external usenet poster
 
Posts: 903
Default Calculating inventory w/ blank field results

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:13 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 OfficeFrustration.
The comments are property of their posters.