Need to return > Value of 2 fields

[songwritingguy]

Greetings.

I am using statuses to track the 'state' of inventory in my warehouse, related to specific shipments. For example:
If I have a shipment calling for Qty 10 of item 'widget',
and 4 of those widgets are picked and packed(Status 400), yet the remaining 6 have not yet been picked (Status 300), then my quantity_at_STS1 = 4, and quantity_at_STS2 = 6.
I have 2 sub queries that I am using in an attempt to return my desired results in my final query which sums all quantities at each status for all shipments combined. The [SumOfQUANTITY_AT_STS1] is coming from the 1st subquery, and the [SumOFQUANTITY_AT_STS2]is coming from my 2nd subquery. However, in my expression shown below, it is only returning results when I have a common STATUS value in both subqueries.

In other words, if I have qty 20 at STS1 and that Status = 400, and qty 10 at STS2 and that Status = 400, then it is returning total qty of 30 which is correct. However, if I have qty 20 at STS1 and that Status = 400, yet '0' at STS2 with no corresponding Status, then I get no result. I want to return the combined qty's, even if there is nothing at STS2. STS1 always has a qty, STS2 does not always have a qty.

I need something that will attempt to Sum the qty's at all statuses, and if there is only a value at the 1st status, then it returns that result.

Here is where I'm running into problems:
SUMQTY: ([SumOfQUANTITY_AT_STS1]+[SumOfQUANTITY_AT_STS2])

Can anyone tell me where I am going wrong? I appologize if this all sounds a bit confusing.

Thanks so much!
Drew

 

[spizotfl]

is the query an "inner join"? if so, then the cases wherer you have nothing on one side of the query are dropping out. if you want to post your sql, that may spur someone to a solution.

 

[songwritingguy]

Thanks spizotfl. Here is my SQL. Perhaps I need an 'if' statement?

SELECT [Orders In WMS Not Shipped Status1].STATUS1, [Orders In WMS Not Shipped Status1].CountOfINTERNAL_SHIPMENT_LINE_NUM, [Orders In WMS Not Shipped Status1].SumOfQUANTITY_AT_STS1, [Orders In WMS Not Shipped Status1].UNITVALUE1, [Orders In WMS Not Shipped Status2].SumOfQUANTITY_AT_STS2, [Orders In WMS Not Shipped Status2].UNITVALUE2, ([SumOfQUANTITY_AT_STS1]+[SumOfQUANTITY_AT_STS2]) AS SUMQTY, ([UNITVALUE1]+[UNITVALUE2]) AS SUMVALUE
FROM [Orders In WMS Not Shipped Status1] LEFT JOIN [Orders In WMS Not Shipped Status2] ON [Orders In WMS Not Shipped Status1].STATUS1 = [Orders In WMS Not Shipped Status2].STATUS2
GROUP BY [Orders In WMS Not Shipped Status1].STATUS1, [Orders In WMS Not Shipped Status1].CountOfINTERNAL_SHIPMENT_LINE_NUM, [Orders In WMS Not Shipped Status1].SumOfQUANTITY_AT_STS1, [Orders In WMS Not Shipped Status1].UNITVALUE1, [Orders In WMS Not Shipped Status2].SumOfQUANTITY_AT_STS2, [Orders In WMS Not Shipped Status2].UNITVALUE2, ([SumOfQUANTITY_AT_STS1]+[SumOfQUANTITY_AT_STS2]), ([UNITVALUE1]+[UNITVALUE2]);

 

[PHV]

Have a look at the Nz function.
BTW why using an aggregate query (group by clause) with no aggregate function ?
SELECT A.STATUS1, A.CountOfINTERNAL_SHIPMENT_LINE_NUM, A.SumOfQUANTITY_AT_STS1, A.UNITVALUE1, B.SumOfQUANTITY_AT_STS2, B.UNITVALUE2, (A.SumOfQUANTITY_AT_STS1+Nz(B.SumOfQUANTITY_AT_STS2,0)) AS SUMQTY, (A.UNITVALUE1+Nz(B.UNITVALUE2,0)) AS SUMVALUE
FROM [Orders In WMS Not Shipped Status1] AS A LEFT JOIN [Orders In WMS Not Shipped Status2] AS B ON A.STATUS1 = B.STATUS2

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[songwritingguy]

Thanks PH for the advice. I tried using the Nz function, but perhaps I did not configure it correctly. In any case, I was successful this morning in acheiving my desired result by using a complex IIF statement.

IIf(IsNull([UNITS1]),0,[UNITS1])+IIf(IsNull([UNITS2]),0,[UNITS2])+IIf(IsNull([UNITS3]),0,[UNITS3]) AS SUMUNITS, IIf(IsNull([VALUE1]),0,[VALUE1])+IIf(IsNull([VALUE2]),0,[VALUE2])+IIf(IsNull([VALUE3]),0,[VALUE3]) AS SUMVALUE

Thanks again.

 

[PHV]

Are you aware that:
IIf(IsNull([UNITS1]),0,[UNITS1])
returns exactly the same value as:
Nz([UNITS1],0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[songwritingguy]

Thanks for the heads up PHV! Was not aware of this 'Nz'