Inventory counting query.

[Guest]

I need help please! It seems as though it should be such a simple query but
I don't know where my mistake is. Just a simple addition accross three
columns for individual records. Could it be because of null values where for
example there is no data entered for 'SignOut' or 'Receiving' but there is
for 'Return'?

SELECT ToolCribSignOut.ToolCribDesignation, (ToolCribSignOut.ReturnQuantity
+ ToolCribSignOut.ReceivingQuantity - ToolCribSignOut.SignOutQuantity) as
(InStock)
From ToolCribSignOut
Group by ToolCribSignOut.ToolCribDesignation;

Is there a solution to my problem?

 

[Allen Browne]

Yes, Nulls will do it.
Use Nz() to replace each null with zero:

SELECT ToolCribSignOut.ToolCribDesignation,
Nz(ToolCribSignOut.ReturnQuantity,0)
+ Nz(ToolCribSignOut.ReceivingQuantity,0)
- Nz(ToolCribSignOut.SignOutQuantity,0) AS InStock
From ToolCribSignOut
Group by ToolCribSignOut.ToolCribDesignation;

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[John Spencer]

Use the NZ function to force a Zero into the equation when the field is
null.

I would use
SELECT ToolCribSignOut.ToolCribDesignation,
Sum(Nz(ReturnQuantity,0) + NZ(ReceivingQuantity,0) - NZ(SignOutQuantity,0))
as InStock
From ToolCribSignOut
Group by ToolCribSignOut.ToolCribDesignation;

Or perhaps
NZ(Sum(ReturnQuantity),0) + Nz(Sum(ReceivingQuantity),0) +
Nz(Sum(SignOutQuantity),0)

 

[Guest]

Alright!!
You guys are great. Thanks very much. Works like a charm.

Cheers.