Getting Syntax Error whilst applying Totals in Query | Newbie | | Join Date: Aug 2009
Posts: 13
| |
I am receiving the following error when attempting to run my query. In my mind - this error should not be happening - its a straight-forward query with a subquery. I am using Access 2003 with all the latest patches. Quote: Microsoft Office Access
Syntax error. in query expression '(SELECT SUM(SQH.Hours) FROM Task AS SQT RIGHT JOIN (Company AS SQC RIGHT JOIN Hours AS SQH ON SQC.CompanyID = SQH.Company) ON SQT.TaskID = SQH.TASK WHERE SQH.WeekEnding <=H.WeekEnding AND SQC.Group=C.Group AND SQT.Group=T.GROUP)'.
If I do not group the query (ie. remove aggregation) it will work. If I recall, it also works if my subquery does not have joins.
I want to accomplish this with pure SQL .. I could easily write a VBA function to handle this - but it opens up a bigger can of worms for me later down the road.
I have attached the example I was working in. You can see that and skip the rest of this message - or you can review the following description.
Thank you in advance - this has had me stumped for a number of days. Database Structure: Three Tables: Hours, Company, and Task
Company and Task each have 3 fields: ID, Name, and Group
Sample Company Data:
CompanyID=DA
Name=Derrick Assurance
Group=Insurance Sample Task Data:
TaskID=OAC
Name=Microsoft Access Support
Group=Application Support Hours table has 5 fields: AutoNumber, Date, Company, Task, and Hours
Sample Hour Data:
id=28
WeekEnding=#Aug 2, 2009#
Company=DA
Task=OAC
Hours=11 The goal of the query is to have five result fields: WeekEnding Date
Company Group
Task Group
Hours for this WeekEnding
Hours to Date (Sum of hours for this Company Group and Task Group up until this WeekEnding Date) I have written the following query to accomplish this (it has been formatted for readability) -
SELECT H.WeekEnding,
-
C.Group AS CG,
-
T.Group AS TG,
-
Sum(H.Hours) AS WeeklyTotal,
-
(SELECT SUM(SQH.Hours)
-
FROM Task AS SQT
-
RIGHT JOIN (Company AS SQC
-
RIGHT JOIN Hours AS SQH
-
ON SQC.CompanyID = SQH.Company)
-
ON SQT.TaskID = SQH.Task
-
WHERE SQH.WeekEnding<=H.WeekEnding AND
-
SQC.Group=C.Group AND
-
SQT.Group=T.GROUP) AS ToDateTotal
-
FROM Task AS T
-
RIGHT JOIN (Company AS C
-
RIGHT JOIN Hours AS H
-
ON C.CompanyID = H.Company)
-
ON T.TaskID = H.Task
-
GROUP BY H.WeekEnding,
-
C.Group,
-
T.Group,
-
(SELECT SUM(SQH.Hours)
-
FROM Task AS SQT
-
RIGHT JOIN (Company AS SQC
-
RIGHT JOIN Hours AS SQH
-
ON SQC.CompanyID = SQH.Company)
-
ON SQT.TaskID = SQH.Task
-
WHERE SQH.WeekEnding<=H.WeekEnding AND
-
SQC.Group=C.Group AND
-
SQT.Group=T.GROUP);
-
This query results in the Syntax Error in query expression error.
| |
best answer - posted by Yesurbius |
Thanks. I tried with an INNER JOIN within the Subquery, and also with an INNER JOIN in both the Subquery and Main Query - but to no avail.
I got it working however .. I separated out the Subquery and the Aggregate from the same query. I first computed the value using the subquery, then I aggregated those results in a separate parent query.
I think its way more messy this way - and probably a lot more table scans are going to take place ... if someone can chisel the SQL down a bit more - it would be great ...
Here is the working SQL (formatted for readability)
-
SELECT WeekEnding,
-
CG,
-
TG,
-
SUM(Weekly) AS WeeklyTotal,
-
ToDateTotal
-
FROM (SELECT H.WeekEnding,
-
C.Group AS CG,
-
T.Group AS TG,
-
H.Hours AS Weekly,
-
(SELECT Sum(Hours)
-
FROM Task INNER JOIN (
-
Company INNER JOIN
-
Hours
-
ON Company.CompanyID = Hours.Company)
-
ON Task.TaskID = Hours.Task
-
WHERE Company.Group=C.Group AND
-
Task.Group=T.Group AND
-
Hours.WeekEnding<=H.WeekEnding) AS ToDateTotal
-
FROM Task AS T INNER JOIN (
-
Company AS C INNER JOIN
-
Hours AS H
-
ON C.CompanyID = H.Company)
-
ON T.TaskID = H.Task) MSQ
-
GROUP BY WeekEnding,
-
CG,
-
TG,
-
ToDateTotal
| | Expert | | Join Date: Jul 2009 Location: KY
Posts: 244
| | | re: Getting Syntax Error whilst applying Totals in Query
I think your issue is that you have an instance of right joins, then in your sub query you are attempting to do an equal join (inner join) which if I understand correctly cannot be done in the same query. So I have attached your mdb with the new queries, you would need to confirm the data.
Let me know if this works for you/this is what you needed.
-AJ
| | Newbie | | Join Date: Aug 2009
Posts: 13
| | | re: Getting Syntax Error whilst applying Totals in Query
AJ, thanks for the quick reply.
In short, no, that is not what I needed. But you may be right about the JOIN syntax - I will play with that a bit while I wait for other answers.
If the query ran correctly .. and you filtered by Education and Development .. you would get the following results: -
WeekEnding Sector Work Weekly Total To-Date Total
-
09-Aug-09 Education Development 79 79
-
16-Aug-09 Education Development 74 153
-
30-Aug-09 Education Development 100 253
Your query has the to-date totals matching the weekly totals. As I said, I will investigate the joins.
| | Expert | | Join Date: Jul 2009 Location: KY
Posts: 244
| | | re: Getting Syntax Error whilst applying Totals in Query
Ah, I see what you need, you want a running sum. My bad. I will look into that, and I welcome anyone else getting an answer before myself. Cause I am at work, and may not get to this immediately.
-AJ
| | Newbie | | Join Date: Aug 2009
Posts: 13
| | | re: Getting Syntax Error whilst applying Totals in Query
Thanks. I tried with an INNER JOIN within the Subquery, and also with an INNER JOIN in both the Subquery and Main Query - but to no avail.
I got it working however .. I separated out the Subquery and the Aggregate from the same query. I first computed the value using the subquery, then I aggregated those results in a separate parent query.
I think its way more messy this way - and probably a lot more table scans are going to take place ... if someone can chisel the SQL down a bit more - it would be great ...
Here is the working SQL (formatted for readability) -
SELECT WeekEnding,
-
CG,
-
TG,
-
SUM(Weekly) AS WeeklyTotal,
-
ToDateTotal
-
FROM (SELECT H.WeekEnding,
-
C.Group AS CG,
-
T.Group AS TG,
-
H.Hours AS Weekly,
-
(SELECT Sum(Hours)
-
FROM Task INNER JOIN (
-
Company INNER JOIN
-
Hours
-
ON Company.CompanyID = Hours.Company)
-
ON Task.TaskID = Hours.Task
-
WHERE Company.Group=C.Group AND
-
Task.Group=T.Group AND
-
Hours.WeekEnding<=H.WeekEnding) AS ToDateTotal
-
FROM Task AS T INNER JOIN (
-
Company AS C INNER JOIN
-
Hours AS H
-
ON C.CompanyID = H.Company)
-
ON T.TaskID = H.Task) MSQ
-
GROUP BY WeekEnding,
-
CG,
-
TG,
-
ToDateTotal
|  | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,657
| | | re: Getting Syntax Error whilst applying Totals in Query
Can I just say, that anyone who takes that much care with their questions is welcome to post more any time.
It was a real pleasure to come across this thread. The SQL was laid out so intelligently. I was impressed. It was so much easier to understand than the same SQL dumped out as it usually is - straight from an Access QueryDef.
Congratulations to you & Welcome to Bytes!
| | Newbie | | Join Date: Aug 2009
Posts: 13
| | | re: Getting Syntax Error whilst applying Totals in Query
Thanks.
I am often sending SQL statements to DBAs and other developers at work .. you quickly learn that formatting your code will get you quicker responses.
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,657
| | | re: Getting Syntax Error whilst applying Totals in Query
You clearly did!
A million other people with the same experiences clearly didn't ever notice and still post the same SQL, regurgitated directly onto the page. It's a refreshing change.
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,223 network members.
|
