Aggregate function error message

[cheyenne2002]

I have the following code behind my query and I keep getting a strange error.
====code====
SELECT tLots.PropertyNo, tLots.SubDivision, tLots.County, tLots.ST, tLots.PID, tLots.LegDesc, tLots.[Market$], tTaxPaid.TaxPaid, tTaxPaid.TaxDue, tTaxPaid.TaxYear, Sum([tTaxPaid]![TaxPaid]) AS TotalTax
FROM tLots INNER JOIN tTaxPaid ON tLots.PropertyNo = tTaxPaid.PropertyNo
WHERE (((tTaxPaid.TaxDue)=Yes) AND ((tTaxPaid.TaxYear)=[Enter Tax Year]));

---Error----
You tried to execute a query that does not include the specified expression 'ProperyNo' as part of an aggregate function.

----

Can anyone explain what the error is trying to tell me. I do have the PropertyNo as part of the query.

PS: I created the query using Access and not directly with SQL as I am really shakey with SQL coding.

Sami

 

[hneal98]

You need to rewrite your query with a Group By clause like this:

SELECT 
    tLots.PropertyNo, 
    tLots.SubDivision, 
    tLots.County, 
    tLots.ST, 
    tLots.PID, 
    tLots.LegDesc, 
    tLots.[Market$], 
    tTaxPaid.TaxPaid, 
    tTaxPaid.TaxDue, 
    tTaxPaid.TaxYear, 
    Sum([tTaxPaid]![TaxPaid]) AS TotalTax
FROM 
    tLots 
    INNER JOIN tTaxPaid ON tLots.PropertyNo = tTaxPaid.PropertyNo
WHERE 
    (((tTaxPaid.TaxDue)=Yes) 
    AND ((tTaxPaid.TaxYear)=[Enter Tax Year]))
Group by
    tLots.PropertyNo, 
    tLots.SubDivision, 
    tLots.County, tLots.ST, 
    tLots.PID, tLots.LegDesc, 
    tLots.[Market$], 
    tTaxPaid.TaxPaid, 
    tTaxPaid.TaxDue, 
    tTaxPaid.TaxYear;

This is because you have the Sum function and are doing a summary.

 

[cheyenne2002]

Great
Now it works
Thank you.

Sami

 

[hneal98]

No problem.

 

[PHV]

Now it works
Really ? Isn't TotalTax the same as TaxPaid in ALL rows ?

 

[cheyenne2002]

Yes, I think so.

 

[hneal98]

PHV is correct. You don't want to have TaxPaid!TaxPaid, if you are summing it as well, because then you will not be getting a sum, you will be getting taxpaid on each row.

Good catch PHV. I did not see that.