subquery error

[Xipooo]

True, but I don't think the original subquery would have worked anyway, with multiple fields in the SELECT and the fact that it could return multiple records. It could have worked if it were joined in the FROM clause, but wasn't necessary in either case. I don't think you can join to it the way you have it (it would need to be in the FROM clause, not the SELECT clause). Plus you've left out the comma too.

You're right, it needs more cleanup. This looks a bit better.

SELECT  tblCust.Custid, qrygross.[grossreceipts], Max(qrygross.SumOfgrossreceipts) AS [MaxOfSumOfgross receipts]
FROM (SELECT tblCust.Custid, tblgross.grossreceipts, Sum(tblgross.grossreceipts)AS [SumOfgrossreceipts]
FROM tblgross LEFT JOIN tblCust ON tblgross.custid = tblCust.custid
GROUP BY tblCust.Custid, tblgross.grossreceipts, tblCust.costid) as qrygross
RIGHT JOIN tblCust ON qrygross.custid= tblCust.custid;

[slimjen]

Ok. I jump for joy a little too soon. I just realized the query you suggested did not Max the SumOfGrossReceipts; but Max(grossreceipts) instead. The results are different. When I tried to change it to Max(SumOfgrossreceipts) I receive the same error I was getting before.