Query with criteria<>0 problem

[jancliff]

I am struggling with this and going round in circles and was wondering if someone could point me in the right direction.
I have a query with fields (from same table tbl_Sales);
SalesLabourSale , SalesPartsSale, SalesAdmin, SalesCourier, Overpayment
WriteOff, Underpayment

I have tried to introduce an expression called TotalSDNI, that says;
TotalSDNI: [SalesLabourSale]+[SalesPartsSale]+[SalesCourier]+[SalesAdmin]+[OverPayment]-[Underpayment]-[WriteOff]
Which appears to calculate everything ok.

I have tried to create another query that looks at another query that sums all the InvoiceAmount fields (from a different table)
When I then try to create a new query with these two querys and create an expression called Outstanding , Outstanding: nz([SumOfInvoiceAmount])-[TotalSDNI]
This works fine if I do not put any criteria in it but as soon as I say <>0 then it says "you tried to execute a query that does not include the specified expression "not nz([SumOfInvoiceAmount])-[TotalSDNI]=0 as part of an aggregate function.
To say I am confused is an understatement!!!
Many thanks in anticipation....

 

[llkhoutx]

Use the NZ funtion on each field, not the result. Any Null/Empty field would cause the result to be bad, if only the result was NZ'd.

 

[jancliff]

I think Im in a mess;
I have a qry called qrySDNITotals which is below;
SELECT [additions]-[deductions] AS TotalSDNI, [tbl_Sales].[UnderPayment]+[tbl_Sales].[WriteOff] AS Deductions, [tbl_Sales].[SalesAdmin]+[tbl_Sales].[SalesLabourSale]+[tbl_Sales].[SalesPartsSale]+[tbl_Sales].[SalesCourier]+[tbl_Sales].[OverPayment] AS Additions, tbl_Sales.SalesPeriod, tbl_Sales.WeekID, tbl_Sales.SalesID, nz([tbl_Sales].[SalesLabourSale],0) AS LabourCost, nz([tbl_Sales].[SalesPartsSale],0) AS PartsCost, nz([tbl_Sales].[SalesCourier],0) AS CourierCost, tbl_Sales.ContractID, tbl_Sales.SalesDateEntered, nz([tbl_Sales].[SalesAdmin],0) AS AdminCost, nz([tbl_Sales].[WriteOff],0) AS WriteOffCost, nz([tbl_Sales].[Underpayment],0) AS UnderCost, nz([tbl_Sales].[OverPayment],0) AS OverCost
FROM tbl_Sales
GROUP BY [tbl_Sales].[UnderPayment]+[tbl_Sales].[WriteOff], [tbl_Sales].[SalesAdmin]+[tbl_Sales].[SalesLabourSale]+[tbl_Sales].[SalesPartsSale]+[tbl_Sales].[SalesCourier]+[tbl_Sales].[OverPayment], tbl_Sales.SalesPeriod, tbl_Sales.WeekID, tbl_Sales.SalesID, nz([tbl_Sales].[SalesLabourSale],0), nz([tbl_Sales].[SalesPartsSale],0), nz([tbl_Sales].[SalesCourier],0), tbl_Sales.ContractID, tbl_Sales.SalesDateEntered, nz([tbl_Sales].[SalesAdmin],0), nz([tbl_Sales].[WriteOff],0), nz([tbl_Sales].[Underpayment],0), nz([tbl_Sales].[OverPayment],0);

And a query called qryInvoiceTotals as below;
SELECT tbl_Invoice.SalesID, Sum(nz([InvoiceAmount],0)) AS InvoiceTotal
FROM tbl_Invoice
GROUP BY tbl_Invoice.SalesID;

And a query that uses both the above called qry days outstanding
SELECT qrySDNITotals.SalesPeriod, qrySDNITotals.WeekID, Sum(qrySDNITotals.TotalSDNI) AS SumOfTotalSDNI, qrySDNITotals.SalesID, qryInvoiceTotals.InvoiceTotal, [TotalSDNI]-[InvoiceTotal] AS Outstanding, tbl_Contract.CotractName, qrySDNITotals.LabourCost, qrySDNITotals.PartsCost, qrySDNITotals.CourierCost, qrySDNITotals.AdminCost, qrySDNITotals.SalesDateEntered, Now()-[SalesDateEntered] AS DaysOutstanding
FROM (qryInvoiceTotals RIGHT JOIN qrySDNITotals ON qryInvoiceTotals.SalesID = qrySDNITotals.SalesID) INNER JOIN tbl_Contract ON qrySDNITotals.ContractID = tbl_Contract.ContractID
GROUP BY qrySDNITotals.SalesPeriod, qrySDNITotals.WeekID, qrySDNITotals.SalesID, qryInvoiceTotals.InvoiceTotal, [TotalSDNI]-[InvoiceTotal], tbl_Contract.CotractName, qrySDNITotals.LabourCost, qrySDNITotals.PartsCost, qrySDNITotals.CourierCost, qrySDNITotals.AdminCost, qrySDNITotals.SalesDateEntered, Now()-[SalesDateEntered]
ORDER BY Now()-[SalesDateEntered] DESC;

IF I dont put a criteria in of <>0 in the field Outstanding: [TotalSDNI]-[InvoiceTotal] it runs great.

But I need the <>0 to show me in a report just the outstanding amount. I tried doing the nz function on the fields but still get the error message You tried to execute a query that does not include the specified expression "Not [TotalSDNI]-[InvoiceTotal]=0 as part of an aggregate function.
I think Ive bitten off more than I can chew. I expect Ive gone about it in a long way.

 

[RV]

You need to group on [TotalSDNI]-[InvoiceTotal]

RV

 

[Brianwarnock]

I don't think Outstanding: [TotalSDNI]-[InvoiceTotal] is an aggregate expression try Outstanding: Sum([TotalSDNI]-[InvoiceTotal])

Brian

 

[RV]

I don't think Outstanding: [TotalSDNI]-[InvoiceTotal] is an aggregate expression try Outstanding: Sum([TotalSDNI]-[InvoiceTotal])

Brian

That's not what the error message is 'bout.
Aggregate refers to the fact that an aggregation function is used.
In a query using aggregate functions you need to group on each and every column you didn't include in an aggregation function.

As far as I recall, you can't refer to aliases in GROUP BY's.

RV

 

[jancliff]

I tried the sum option and it appears to work!!! The group by did not work. I will test it further to see if this does in fact ring true. Many thanks for all your help everyone...greatly appreciated definitely. I only wish I could return the favour by being intelligent enough and brave enough to actually respond to a problem rather than create one!

 

[Brianwarnock]

But using Sum turns it into an aggregate function, using Group by produces a totally different result, which maybe the one he wants , but I doubt it.

Brian

I see jancliff replied before I did.

 

[jancliff]

I tried the sum option and it appears to work!!! The group by did not work. I will test it further to see if this does in fact ring true. Many thanks for all your help everyone...greatly appreciated definitely. I only wish I could return the favour by being intelligent enough and brave enough to actually respond to a problem rather than create one!

 

[RV]

But using Sum turns it into an aggregate function, using Group by produces a totally different result, which maybe the one he wants , but I doubt it.

You're quite right
Including [TotalSDNI]and [InvoiceTotal] in the GROUP BY clause should also work though, although the retrieved row will be grouped slightly different.

RV