You tried to execute a query that does not include the specified expression 'salesID'

[inte]

Hi everyone
I get this error when every I try to execute a particular query in my db.
""You tried to execute a query that does not include the specified expression 'salesID' as part of an aggregated function""

This is the SQL

SELECT tblSalesMAIN.salesID, tblSalesMAIN.salesTime, tblSalesMAIN.salesDate, tblSalesMAIN.unitPrice, tblSalesMAIN.quantity, tblSalesMAIN.cashTendered, tblSalesMAIN.unitPriceID, [txtCashTendered]-Sum([quantity]*[unitPrice]) AS txtChange, [quantity]*[unitPrice] AS txtPrice, tblSalesMAIN.productName
FROM tblSalesMAIN;

What is it that I am missing?

[Nauticalgent]

Usually this means that you are including a field name in your SQL statement that does not exist. I assume there is infact a field named SalesID in your table tblSalesMain?

[PhilOfWalton]

You have 2 errors.

1) The field in your table is CashTenderd, not txtCashTenderd.

2) There is nothing to Sum. You only have a single record as SaledID is unique.

Your SQL should be

SELECT tblSalesMAIN.salesID, tblSalesMAIN.salesTime, tblSalesMAIN.salesDate, tblSalesMAIN.unitPrice, tblSalesMAIN.quantity, tblSalesMAIN.cashTendered, tblSalesMAIN.unitPriceID, [CashTendered]-[quantity]*[unitPrice] AS txtChange, [quantity]*[unitPrice] AS txtPrice, tblSalesMAIN.ProductName
FROM tblSalesMAIN;
 

Unfortunately, this is not going to achieve what I think you are wanting.
My guess is that you want a customer to buy a number of items, check the total and work out the change.

I suspect you need to consider your table structure as I think you need a table of Customers (That may not even need a name, just a CustomerID and a date so that all the sales can be "assigned" to that Customer / Date. Also a Product Table containing ProductName and probably UnitPrice.

Phil

[NeoPa]

Whenever you use an aggregation function (Like Sum() for instance) that usage causes the SQL engine to treat your query like an aggregate query. Normally with an aggregate query you would expect a GROUP BY clause, but this isn't absolutely necessary.

So, your Sum() reference makes it an aggregate query. Each reference in the SELECT clause therefore has to be to an aggregation function or to a field included in the GROUP BY clause.

The first field is neither so the SQL engine knows you've got it wrong and lets you know about it.

What you are actually trying to achieve is anybody's guess. We certainly don't have enough information. What we can say, and what SQL has already told you, is that what you have is fundamentally wrong and why.