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: - 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
-
FROM tblProducts INNER JOIN tblSaleDetails ON tblProducts.ProductID = tblSaleDetails.ItemNumber;
-
And here is the code after the sum function is added: - 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
-
FROM tblProducts INNER JOIN tblSaleDetails ON tblProducts.ProductID = tblSaleDetails.ItemNumber
-
GROUP BY tblSaleDetails.ItemNumber, tblSaleDetails.SaleQty, tblProducts.PrdtName, tblProducts.PrdtPrice, tblProducts.PrdtProfit, tblProducts.PrdtTaxable, CCur([SaleQty]*[PrdtProfit]);
-
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
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 : - 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
-
-
FROM tblProducts
-
INNER JOIN
-
tblSaleDetails
-
ON tblProducts.ProductID = tblSaleDetails.ItemNumber
-
-
GROUP BY tblSaleDetails.ItemNumber
-
, tblSaleDetails.SaleQty
-
, tblProducts.PrdtName
-
, tblProducts.PrdtPrice
-
, tblProducts.PrdtProfit
-
, tblProducts.PrdtTaxable
-
, 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.
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?
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 : - 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
-
-
FROM tblProducts
-
INNER JOIN
-
tblSaleDetails
-
ON tblProducts.ProductID = tblSaleDetails.ItemNumber
-
-
GROUP BY tblSaleDetails.ItemNumber
-
, tblSaleDetails.SaleQty
-
, tblProducts.PrdtName
-
, tblProducts.PrdtPrice
-
, tblProducts.PrdtProfit
-
, tblProducts.PrdtTaxable
-
, 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.
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 :)
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.
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.
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 :-)
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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....
|
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;...
|
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"....
|
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...
|
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...
|
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,...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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....
|
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
|
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...
| |