422,951 Members | 2,254 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 422,951 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?
2 Weeks Ago #1
Share this Question
Share on Google+
3 Replies


Nauticalgent
P: 54
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?
2 Weeks Ago #2

PhilOfWalton
Expert 100+
P: 1,202
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;
  3.  
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
2 Weeks Ago #3

NeoPa
Expert Mod 15k+
P: 30,869
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.
2 Weeks Ago #4

Post your reply

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