473,326 Members | 2,104 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Why won't my query work when I add the sum function?

Seth Schrock
2,965 Expert 2GB
I have a rather complicated query that works just fine until I have it add up the columns. When I add the sum function, it says "Subqueries cannot be used in the expression (CCur([SalesTax]+[ItemPrice]*[SaleQty]))."

Here is the code before the sum:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblSaleDetails.ItemNumber, tblSaleDetails.SaleQty, tblProducts.PrdtName, tblProducts.PrdtPrice, tblProducts.PrdtProfit, CCur([SaleQty]*[ItemPrice]) AS SubTotal, tblProducts.PrdtTaxable, Switch([PrdtTaxable]=1,CCur([SaleQty]*[ItemPrice]*DLookUp("TaxRate","tblTaxRate","TaxRateID = 1")),[PrdtTaxable]=2,0,[PrdtTaxable]=3,DLookUp("PrdtTax","tblProducts","ProductID =" & [ItemNumber])) AS SalesTax, CCur([SaleQty]*[PrdtProfit]) AS Profit, CCur([SalesTax]+([ItemPrice]*[SaleQty])) AS TotalPrice
  2. FROM tblProducts INNER JOIN tblSaleDetails ON tblProducts.ProductID = tblSaleDetails.ItemNumber;
  3.  
And here is the code after the sum function is added:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblSaleDetails.ItemNumber, tblSaleDetails.SaleQty, tblProducts.PrdtName, tblProducts.PrdtPrice, tblProducts.PrdtProfit, Sum(CCur([SaleQty]*[ItemPrice])) AS SubTotal, tblProducts.PrdtTaxable, Sum(Switch([PrdtTaxable]=1,CCur([SaleQty]*[ItemPrice]*DLookUp("TaxRate","tblTaxRate","TaxRateID = 1")),[PrdtTaxable]=2,0,[PrdtTaxable]=3,DLookUp("PrdtTax","tblProducts","ProductID =" & [ItemNumber]))) AS SalesTax, CCur([SaleQty]*[PrdtProfit]) AS Profit, Sum(CCur([SalesTax]+([ItemPrice]*[SaleQty]))) AS TotalPrice
  2. FROM tblProducts INNER JOIN tblSaleDetails ON tblProducts.ProductID = tblSaleDetails.ItemNumber
  3. GROUP BY tblSaleDetails.ItemNumber, tblSaleDetails.SaleQty, tblProducts.PrdtName, tblProducts.PrdtPrice, tblProducts.PrdtProfit, tblProducts.PrdtTaxable, CCur([SaleQty]*[PrdtProfit]);
  4.  
I'm not very good with SQL code, so I don't how to fix the problem. I'm hoping that there is away around this, but I don't know. I tried to save my database as a Access 2003 file, but evidently I'm using feature that are only available in Access 2010 (the version I'm using). Let me know if you need me to recreate the database in a previous version and I'll attach it then. If it helps, here is the table structures:

tblSaleDetails
DetailID PK
SaleID
ItemNumber
SaleQty
ItemName
ItemPrice
UnitProfit


tblProducts
ProductID PK
PrdtName
PrdtProfit
PrdtPrice
PrdtTax
PrdtTaxable
PrdtTotal
CategoryID
Oct 18 '11 #1

✓ answered by NeoPa

If you reread my post Seth, you'll see I made a specifically general statement. Your SQL is not laid out to be easily checked. It's fine for the SQL engine like that, but if I'm to work with it then I'd need to copy/paste it somewhere else to see what it says (Not practical on my iPhone). I've done that now, and for the benefit of anyone else trying to follow the thread I'll repost your SQL in a readable form :

Expand|Select|Wrap|Line Numbers
  1. SELECT   tblSaleDetails.ItemNumber
  2.        , tblSaleDetails.SaleQty
  3.        , tblProducts.PrdtName
  4.        , tblProducts.PrdtPrice
  5.        , tblProducts.PrdtProfit
  6.        , Sum(CCur([SaleQty]*[ItemPrice])) AS SubTotal
  7.        , tblProducts.PrdtTaxable
  8.        , Sum(Switch([PrdtTaxable]=1,CCur([SaleQty]*[ItemPrice]*DLookUp("TaxRate","tblTaxRate","TaxRateID = 1")),[PrdtTaxable]=2,0,[PrdtTaxable]=3,DLookUp("PrdtTax","tblProducts","ProductID =" & [ItemNumber]))) AS SalesTax
  9.        , CCur([SaleQty]*[PrdtProfit]) AS Profit
  10.        , Sum(CCur([SalesTax]+([ItemPrice]*[SaleQty]))) AS TotalPrice
  11.  
  12. FROM     tblProducts
  13.          INNER JOIN 
  14.          tblSaleDetails
  15.   ON     tblProducts.ProductID = tblSaleDetails.ItemNumber
  16.  
  17. GROUP BY tblSaleDetails.ItemNumber
  18.        , tblSaleDetails.SaleQty
  19.        , tblProducts.PrdtName
  20.        , tblProducts.PrdtPrice
  21.        , tblProducts.PrdtProfit
  22.        , tblProducts.PrdtTaxable
  23.        , CCur([SaleQty]*[PrdtProfit]);
In Line #10 you use a Sum()ed value ([SalesTax]) within another Sum() call.

PS. Are you aware of the Choose() function? It may suit your SQL better than the Switch() you're using as long as the checked values ([PrdtTaxable]) are limited to 1, 2 & 3.

7 7231
NeoPa
32,556 Expert Mod 16PB
Hard to explain from phone, but question well asked so I'll post a brief explanation now.

What you need to know is that when you use an aggregate, like Sum(), then all fields in the SELECT clause must either be GROUPED BY or aggregated fields.

What you ask for is not possible, but when you think about it, it can't possibly make sense either.

If that doesn't make sense after consideration let me know and I'll explain.
Oct 18 '11 #2
Seth Schrock
2,965 Expert 2GB
I just checked, and all the fields that don't have the Sum() function before them are in the Group By section at the end. The aliases names themselves aren't part of it, but the calculation is.

What part "can't possibly make sense"? I'm trying to copy the functionality of the Northwind sample database. Something I just noticed was the fact that in Northwind, the query itself didn't use the sum feature, but the form based on the query had the sum of some of the columns. I could do that, but I'm not sure how they did that yet. Is that an option that would work?
Oct 18 '11 #3
NeoPa
32,556 Expert Mod 16PB
If you reread my post Seth, you'll see I made a specifically general statement. Your SQL is not laid out to be easily checked. It's fine for the SQL engine like that, but if I'm to work with it then I'd need to copy/paste it somewhere else to see what it says (Not practical on my iPhone). I've done that now, and for the benefit of anyone else trying to follow the thread I'll repost your SQL in a readable form :

Expand|Select|Wrap|Line Numbers
  1. SELECT   tblSaleDetails.ItemNumber
  2.        , tblSaleDetails.SaleQty
  3.        , tblProducts.PrdtName
  4.        , tblProducts.PrdtPrice
  5.        , tblProducts.PrdtProfit
  6.        , Sum(CCur([SaleQty]*[ItemPrice])) AS SubTotal
  7.        , tblProducts.PrdtTaxable
  8.        , Sum(Switch([PrdtTaxable]=1,CCur([SaleQty]*[ItemPrice]*DLookUp("TaxRate","tblTaxRate","TaxRateID = 1")),[PrdtTaxable]=2,0,[PrdtTaxable]=3,DLookUp("PrdtTax","tblProducts","ProductID =" & [ItemNumber]))) AS SalesTax
  9.        , CCur([SaleQty]*[PrdtProfit]) AS Profit
  10.        , Sum(CCur([SalesTax]+([ItemPrice]*[SaleQty]))) AS TotalPrice
  11.  
  12. FROM     tblProducts
  13.          INNER JOIN 
  14.          tblSaleDetails
  15.   ON     tblProducts.ProductID = tblSaleDetails.ItemNumber
  16.  
  17. GROUP BY tblSaleDetails.ItemNumber
  18.        , tblSaleDetails.SaleQty
  19.        , tblProducts.PrdtName
  20.        , tblProducts.PrdtPrice
  21.        , tblProducts.PrdtProfit
  22.        , tblProducts.PrdtTaxable
  23.        , CCur([SaleQty]*[PrdtProfit]);
In Line #10 you use a Sum()ed value ([SalesTax]) within another Sum() call.

PS. Are you aware of the Choose() function? It may suit your SQL better than the Switch() you're using as long as the checked values ([PrdtTaxable]) are limited to 1, 2 & 3.
Oct 18 '11 #4
Seth Schrock
2,965 Expert 2GB
I couldn't figure out what you meant by "hard to explain by phone", but I understand now. Sorry.

Okay, I think I know away around the line 10 problem. And I'm not aware of the Choose() function. I found the Switch() function when I was looking to see if SQL had a Case function and the Switch() function said it acted like a Case function so I used it. The checked values [PrdtTaxable] are limited to 1, 2, & 3. Would it solve the problem, or it is just an easier option?

I'll try to remember to post my SQL in a more readable format :)
Oct 18 '11 #5
NeoPa
32,556 Expert Mod 16PB
Seth:
I'll try to remember to post my SQL in a more readable format :)
It's worth remembering (and the effort) as you're the biggest beneficiary (Experts can and do skip questions when they find understanding the question too much trouble - I know I have).

Choose() and Switch() are both covered in Access's Context-Sensitive Help system. Switch() is better when the various tests are disparate (but that doesn't seem to be the case here).

All fields in the SELECT field list must either be included in the GROUP BY clause or be included as arguments to an SQL aggregate function.

This quote from the Help system implies that all references to fields, even compound references like those you have in your SQL, must either be aggregated (included in one of the aggregate functions - Sum(), Avg(), Min(), Max(), Count(), StDev(), Var(), First() or Last()) or included in the GROUP BY clause. Any expression which is of either type is considered aggregated. The aggregate functions can only take field reference expressions which resolve to non-aggregated fields. This should help you find the, what seem like various, invalid expressions in your SQL.

Good luck.
Oct 18 '11 #6
Seth Schrock
2,965 Expert 2GB
What I ended up doing is run the query without the Sum() Function and then use the Totals feature that is available on forms in Datasheet view. It is a new feature in Access 2007. So now my query works and the form that is based on the query has the totals like I want.
Oct 19 '11 #7
NeoPa
32,556 Expert Mod 16PB
That's fine Seth.

I just want to make clear, for any others reading this thread if not for you, that such an approach isn't necessary (although perfectly valid of course). The aggregating can be done within the query itself as long as every reference is maintained inside, or outside, of the aggregate routines as required (and specified in my previous post (#6).

Great to hear you have your situation resolved anyway :-)
Oct 19 '11 #8

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

Similar topics

4
by: Mark Hayworth | last post by:
Randy: I too am having the problem that this other guy/girl had. I put in the proper API declarations and arguments for GetPrivateProfileString yet it doesn't return the correct stuff. It always...
4
by: shumaker | last post by:
I'm wondering how/why this query works. Trying to get my head wrapped around SQL. Basically the Query deletes from the Import table all records that are already in FooStrings so that when I do an...
1
by: David M. Gauntt | last post by:
I have been testing some javascript for my professional society web page, and found that I couldn't get Safari to save any cookies. No matter what I assign to document.cookie, I get an empty...
9
by: Roger Withnell | last post by:
Tearing hair out time! Simple attached page shows the problem. http://www.brilley.co.uk/TestFocusSelect.htm Using a function to test if too many characters have been keyed in to a textarea....
15
by: Eirik | last post by:
This is a little function I wrote, inspired by the thread "Urgent HELP! required for Caesar Cipher PLEASE" $ cat /home/keisar/bin/c/ymse/rot13.h char rot13(char character) { int changed;...
16
by: pshyamsunder | last post by:
Hi: I am facing a strange problem. I have opened a file "File1" using fopen() and while processing the records in the file in a loop, I have a call to access() to check existence of "File2"....
19
by: d_goto | last post by:
I am trying to do a simple login page. However, even when I input a correct user name and password, I get the "You are not authorized!" display. If anyone could looks over my code and see if...
1
by: cookieplanter | last post by:
I need to add content to two DIVs on page load but this just won't work. If I call any one function, things work fine or if I trigger the events on-click, it works perfectly. But with both together...
0
by: kc7zdm | last post by:
I'm using Oracle intermedia text indices in my database, and the Contains function from a VB.Net program using the system.data.oracleclient provider. When a query includes a number as a search term,...
2
by: esource | last post by:
Hi all, I'm trying to use threading model in my web service but addressof does not work with functions I'm using VS 2005 my code: Public Function Start() as Boolean Dim MyThread As New...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.