Nz function
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.
|
