424,303 Members | 1,335 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,303 IT Pros & Developers. It's quick & easy.

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

P: 9
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
Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM tblSalesMAIN;
What is it that I am missing?
Jul 7 '18 #1
Share this Question
Share on Google+
3 Replies

P: 80
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?
Jul 7 '18 #2

Expert 100+
P: 1,427
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
Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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.

Jul 7 '18 #3

Expert Mod 15k+
P: 31,186
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.
Jul 7 '18 #4

Post your reply

Sign in to post your reply or Sign up for a free account.