Nz function

[rjmccafferty1]

I am trying to subtract one field from another. At the moment, there are values in one field, but not in the other. Thus, the result of the subtractions should equal the values in the field has entries.

Both of these selected "tables" for the query are actually sub-queries that total invoice amounts by customer. The results of one of the sub queries shows the appropriate numbers. And, since there are no entries yet in the other field, that query yields no results. Thus, I believe that the "answers" to the calculations should exactly equal the entries in the table with values.

Of course I need to convert all those pesky null values to zeros. Rather than getting the expected long list of results, I am getting no result.

(The advantage with keeping 1 table free of any entries is that it isolates the issue of when there really is no number for some customers in the real data)

The SQL for this query that calculates the difference in the two totals is:
SELECT DISTINCTROW Qry_Sub2_TotalInvByCustID.SumOfInvoiceCommisions, Qry_Sub2_TotalCommPdByCustID.SumOfCommPd, Qry_Sub2_TotalCommPdByCustID.[CUSTOMER ID], (Nz([Qry_Sub2_TotalCommPdByCustID].[SumOfCommPd],0)-Nz([Qry_Sub2_TotalInvByCustID].[SumOfInvoiceCommisions],0))/Nz([Qry_Sub2_TotalCommPdByCustID].[SumOfCommPd],0) AS CommVariance
FROM Qry_Sub2_TotalCommPdByCustID INNER JOIN Qry_Sub2_TotalInvByCustID ON Qry_Sub2_TotalCommPdByCustID.[CUSTOMER ID] = Qry_Sub2_TotalInvByCustID.CustID
ORDER BY (Nz([Qry_Sub2_TotalCommPdByCustID].[SumOfCommPd],0)-Nz([Qry_Sub2_TotalInvByCustID].[SumOfInvoiceCommisions],0))/Nz([Qry_Sub2_TotalCommPdByCustID].[SumOfCommPd],0) DESC;

Then again, possibly the problem is in the query that calculates totals for the table with no entries. The SQL for that query is:
SELECT DISTINCTROW Sum(Qry_Sub_InvoicesForMonth.MonthlyCommAmt) AS SumOfInvoiceCommisions, Qry_Sub_InvoicesForMonth.CustID
FROM Qry_Sub_InvoicesForMonth
GROUP BY Qry_Sub_InvoicesForMonth.CustID;

I would sure appreciate any help with making this work.

 

[MazeWorX]

in the underlying table if you set the default value to 0 then you will always have a number to do the math

Thoughts

HTH << MaZeWorX >> Remember amateurs built the ark - professionals built the Titanic [flush]

 

[JoeAtWork]

MazeWorX - as the OP points out, it is sometimes necessary to distinguish whether the number is actually "confirmed to be zero" as opposed to "number is unknown".

rjmccafferty1 - you've stated your goal but not really said what the problem is. However, this little snippet...

(Nz([Qry_Sub2_TotalCommPdByCustID].[SumOfCommPd],0)-Nz([Qry_Sub2_TotalInvByCustID].[SumOfInvoiceCommisions],0))/Nz([Qry_Sub2_TotalCommPdByCustID].[SumOfCommPd],0)

...looks to me that you could end up with a division by zero error if SumOfCommPd is Null, or even it happens to add up to zero.