Hi Guys.
Could do with another set of eyes on this expression, where am I going wrong?
Tot Parts: IIf([Credited]>0,(Sum(Nz([qryPartsInv.ItmValue]-[Credited],(Sum(Nz([qryPartsInv.ItmValue])))))))
Cannot have aggregate function in expression
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Cannot have aggregate function in expression
Cheers ...
Dave.
Dave.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Cannot have aggregate function in expression
You can't mix a single field value (Credited) with an aggregate function (Sum), unless you group by the single field (Credited) in a Totals query.
Perhaps you can use
TotParts: DSum("ItmValue","qryPartsInv")-IIf([Credited]>0,[Credited],0)
Perhaps you can use
TotParts: DSum("ItmValue","qryPartsInv")-IIf([Credited]>0,[Credited],0)
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: Cannot have aggregate function in expression
Thanks Hans
The DSum didn't throw any errors but it has set the value of 'Tot Parts' the same throughout the results.
The expression should look at the value of [Credited] and if it is greater than zero set the value of parts [Tot Parts] to zero. Maybe the AQL will help.
SELECT tblJCV.WorkProviderName AS Type, Count(tblJCV.JobID) AS Volume, Sum(tblJCV.TotalInvoicednet) AS [Tot Nett], Sum(tblJCV.AllocHours) AS [Tot Hrs], Sum(Nz([qryLabInv.ItmValue])) AS [Tot Labour], IIf([Credited]>0,(Sum(Nz([qryPartsInv.ItmValue]-[Credited],(Sum(Nz([qryPartsInv.ItmValue]))))))) AS [Tot Parts], Sum(Nz([qryMatsInv.ItmValue])) AS [Tot Mats], Sum(Nz([qryOtherInv.ItmValue])) AS [Tot Other], tblJCV.JobID, tblJCV.WriteOffDate, Sum(Nz([qryLabInv.ItmNett])) AS [Tot Labour Nett], Sum(IIf(Nz([qryPartsInv.ItmNett])>Nz([qryPartsInv.ItmValue]),Nz([qryPartsInv.ItmValue]),Nz([qryPartsInv.ItmNett]))) AS [Tot Parts Nett], Sum(Nz([qryMatsInv.ItmNett])) AS [Tot Mats Nett], Sum(Nz([qryOtherInv.ItmNett])) AS [Tot Other Nett], Sum(tblJCV.ProdHoursBooked) AS [Hrs Booked], Sum(Nz([SumOfItmNett])) AS ManCC, tblJCV.repex_code AS REPEX, tblJCV.EstimatorCode, Sum(tblCredits.Credited) AS SumOfCredited
FROM tblCredits RIGHT JOIN (qryManCCar RIGHT JOIN (qryPartsInv RIGHT JOIN (qryMatsInv RIGHT JOIN (qryLabInv RIGHT JOIN (qryOtherInv RIGHT JOIN tblJCV ON qryOtherInv.JobID = tblJCV.JobID) ON qryLabInv.JobID = tblJCV.JobID) ON qryMatsInv.JobID = tblJCV.JobID) ON qryPartsInv.JobID = tblJCV.JobID) ON qryManCCar.JobID = tblJCV.JobID) ON tblCredits.JobID = tblJCV.JobID
GROUP BY tblJCV.WorkProviderName, tblJCV.JobID, tblJCV.WriteOffDate, tblJCV.repex_code, tblJCV.EstimatorCode
HAVING (((tblJCV.WorkProviderName) Is Not Null) AND ((tblJCV.WriteOffDate) Is Null) AND ((tblJCV.EstimatorCode) Not In ("Kevin","Tony")));
The DSum didn't throw any errors but it has set the value of 'Tot Parts' the same throughout the results.
The expression should look at the value of [Credited] and if it is greater than zero set the value of parts [Tot Parts] to zero. Maybe the AQL will help.
SELECT tblJCV.WorkProviderName AS Type, Count(tblJCV.JobID) AS Volume, Sum(tblJCV.TotalInvoicednet) AS [Tot Nett], Sum(tblJCV.AllocHours) AS [Tot Hrs], Sum(Nz([qryLabInv.ItmValue])) AS [Tot Labour], IIf([Credited]>0,(Sum(Nz([qryPartsInv.ItmValue]-[Credited],(Sum(Nz([qryPartsInv.ItmValue]))))))) AS [Tot Parts], Sum(Nz([qryMatsInv.ItmValue])) AS [Tot Mats], Sum(Nz([qryOtherInv.ItmValue])) AS [Tot Other], tblJCV.JobID, tblJCV.WriteOffDate, Sum(Nz([qryLabInv.ItmNett])) AS [Tot Labour Nett], Sum(IIf(Nz([qryPartsInv.ItmNett])>Nz([qryPartsInv.ItmValue]),Nz([qryPartsInv.ItmValue]),Nz([qryPartsInv.ItmNett]))) AS [Tot Parts Nett], Sum(Nz([qryMatsInv.ItmNett])) AS [Tot Mats Nett], Sum(Nz([qryOtherInv.ItmNett])) AS [Tot Other Nett], Sum(tblJCV.ProdHoursBooked) AS [Hrs Booked], Sum(Nz([SumOfItmNett])) AS ManCC, tblJCV.repex_code AS REPEX, tblJCV.EstimatorCode, Sum(tblCredits.Credited) AS SumOfCredited
FROM tblCredits RIGHT JOIN (qryManCCar RIGHT JOIN (qryPartsInv RIGHT JOIN (qryMatsInv RIGHT JOIN (qryLabInv RIGHT JOIN (qryOtherInv RIGHT JOIN tblJCV ON qryOtherInv.JobID = tblJCV.JobID) ON qryLabInv.JobID = tblJCV.JobID) ON qryMatsInv.JobID = tblJCV.JobID) ON qryPartsInv.JobID = tblJCV.JobID) ON qryManCCar.JobID = tblJCV.JobID) ON tblCredits.JobID = tblJCV.JobID
GROUP BY tblJCV.WorkProviderName, tblJCV.JobID, tblJCV.WriteOffDate, tblJCV.repex_code, tblJCV.EstimatorCode
HAVING (((tblJCV.WorkProviderName) Is Not Null) AND ((tblJCV.WriteOffDate) Is Null) AND ((tblJCV.EstimatorCode) Not In ("Kevin","Tony")));
You do not have the required permissions to view the files attached to this post.
Cheers ...
Dave.
Dave.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Cannot have aggregate function in expression
It won't work since you don't group by Credited. What exactly do you want to do with Credited if you don't group by it?
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: Cannot have aggregate function in expression
Credited doesn't have to do anything Hans, it's more of an identifier and totally hidden. We know with certain work providers we have to purchase parts and then credit them separately but my report can't distinguish because the sales value of parts is still there. So a separate query 'qryCredits' has been joined so I can identify those cases and set the value of parts sales [Tot Parts] to zero.
Cheers ...
Dave.
Dave.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Cannot have aggregate function in expression
I'm sorry, I don't see how this could work.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: Cannot have aggregate function in expression
I'll have to find a way Hans, I tried this:
Tot Parts: Sum(IIf([Credited]>0,Nz([qryPartsInv.ItmValue],0),Nz([qryPartsInv.ItmValue])))
Made no difference, but it didn't crash out either.
Tot Parts: Sum(IIf([Credited]>0,Nz([qryPartsInv.ItmValue],0),Nz([qryPartsInv.ItmValue])))
Made no difference, but it didn't crash out either.
Cheers ...
Dave.
Dave.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Cannot have aggregate function in expression
The condition [Credited]>0 doesn't do anything in that expression, you return the sum of qryPartsInv.ItmValue whether the condition is true or not.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: Cannot have aggregate function in expression
How about this:
Tot Parts: IIf([Credited]>0,[qryPartsInv.ItmValue]-[qryPartsInv.ItmValue],[qryPartsInv.ItmValue])
Seems to be working ??
Tot Parts: IIf([Credited]>0,[qryPartsInv.ItmValue]-[qryPartsInv.ItmValue],[qryPartsInv.ItmValue])
Seems to be working ??
Cheers ...
Dave.
Dave.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Cannot have aggregate function in expression
That is equivalent to
Tot Parts: IIf([Credited]>0,0,[qryPartsInv.ItmValue])
What did you set the Total option for this column to? Sum?
Tot Parts: IIf([Credited]>0,0,[qryPartsInv.ItmValue])
What did you set the Total option for this column to? Sum?
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: Cannot have aggregate function in expression
Yes it is, I didn't see that...
Yes I set it to Sum and doing a load of testing to see the outcome, for now it's returning something at least.
Many Thanks
Yes I set it to Sum and doing a load of testing to see the outcome, for now it's returning something at least.
Many Thanks
Cheers ...
Dave.
Dave.