423,682 Members | 1,348 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,682 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


Nauticalgent
P: 73
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

PhilOfWalton
Expert 100+
P: 1,373
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
Jul 7 '18 #3

NeoPa
Expert Mod 15k+
P: 31,084
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.