Have no idea why this isn't working... (1 Viewer)

[andreas_udby]

Could I have constructed my joins improperly? Here's the deal:

I have a query that pulls from three other queries:

qrySegment: [Business Segment]

qryHC: [Business Segment] [HC_Sales] [HC_Ops] [HC_Enab] etc.

qry2_Ldr: [Business Segment] [Ldr_EB] [Ldr_SEB] [Ldr_VP] etc.


I'm using the new query to calculate new variables from the previous tables. Here's my SQL statement (tabs and carriage returns provided for clarity):

SELECT qrySegment.[Business Segment],
qryHC.[HC_Sales]+[HC_Ops]+[HC_Enab] AS Ex_Total,
qry2_Ldr.[Ldr_EB]+[Ldr_SEB]+[Ldr_VP] AS Ldr_Total

FROM qry2_Ldr RIGHT JOIN
(qryHC RIGHT JOIN
qrySegment ON
qryHC.[Business Segment] = qrySegment.[Business Segment]) ON
qry2_Ldr.[Business Segment] = qrySegment.[Business Segment];

Now, the new variable "Ex_Total" calculates just fine, but I can't get anything to return for "Ldr_Total", even when I remove the qryHC query and run just the results for the qry2_Ldr calculation. When I select the individual fields from qry2_Ldr, they return just fine also; it's just the calculation that's failing. What have I done wrong?

Thanks,
Andreas

 

[andreas_udby]

Wait a second... I just realized that the "Ldr_VP" variable has no data in it... when I used the Nz function on this thing, my results returned just fine.

So now I have a new question: How do calculations in a query handle Null values? Do they prevent the whole calculation from working, as seems to be happening in my query? Is there a way to force a query with Null values in it to treat them as or replace them with zeros?

 

[Pat Hartman]

Aggregate functions are designed to handle null values correctly. This is explained in help but I don't remember where. For example, if you have three rows:
xx 4
yy null
zz 8

And you use the aggregate function Avg(SomeField), the answer will be 6 NOT 4 and Sum(SomeField) will be 12. Count(SomeField) will be 2 BUT Count(*) will be 3.

However, as you have seen, arithmetic operations fail. Therefore, you need to use the Nz() function in expressions involvine atithmetic operations.

 

[andreas_udby]

Thanks, Pat. I appreciate the insight.