By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,542 Members | 1,740 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,542 IT Pros & Developers. It's quick & easy.

Getting Syntax Error whilst applying Totals in Query

P: 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.

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)
Expand|Select|Wrap|Line Numbers
  1. SELECT     H.WeekEnding, 
  2.     C.Group AS CG, 
  3.     T.Group AS TG, 
  4.     Sum(H.Hours) AS WeeklyTotal, 
  5.     (SELECT     SUM(SQH.Hours) 
  6.         FROM     Task AS SQT 
  7.                 RIGHT JOIN (Company AS SQC 
  8.                     RIGHT JOIN Hours AS SQH 
  9.                     ON SQC.CompanyID = SQH.Company) 
  10.                 ON SQT.TaskID = SQH.Task 
  11.         WHERE     SQH.WeekEnding<=H.WeekEnding AND 
  12.             SQC.Group=C.Group AND 
  13.             SQT.Group=T.GROUP) AS ToDateTotal
  14. FROM     Task AS T 
  15.         RIGHT JOIN (Company AS C 
  16.             RIGHT JOIN Hours AS H 
  17.             ON C.CompanyID = H.Company) 
  18.         ON T.TaskID = H.Task
  19. GROUP BY H.WeekEnding, 
  20.     C.Group, 
  21.     T.Group, 
  22.     (SELECT SUM(SQH.Hours) 
  23.         FROM     Task AS SQT 
  24.                 RIGHT JOIN (Company AS SQC 
  25.                     RIGHT JOIN Hours AS SQH 
  26.                     ON SQC.CompanyID = SQH.Company) 
  27.                 ON SQT.TaskID = SQH.Task 
  28.         WHERE     SQH.WeekEnding<=H.WeekEnding AND 
  29.             SQC.Group=C.Group AND 
  30.             SQT.Group=T.GROUP);
  31.  
This query results in the Syntax Error in query expression error.
Attached Files
File Type: zip example.zip (18.7 KB, 68 views)
Aug 27 '09 #1

✓ answered 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)

Expand|Select|Wrap|Line Numbers
  1. SELECT  WeekEnding, 
  2.         CG, 
  3.         TG, 
  4.         SUM(Weekly) AS WeeklyTotal, 
  5.         ToDateTotal 
  6. FROM    (SELECT H.WeekEnding, 
  7.                 C.Group AS CG, 
  8.                 T.Group AS TG, 
  9.                 H.Hours AS Weekly, 
  10.                 (SELECT Sum(Hours) 
  11.                  FROM   Task    INNER JOIN (
  12.                         Company INNER JOIN 
  13.                         Hours
  14.                            ON Company.CompanyID = Hours.Company)
  15.                            ON Task.TaskID = Hours.Task 
  16.                  WHERE  Company.Group=C.Group AND 
  17.                         Task.Group=T.Group AND 
  18.                         Hours.WeekEnding<=H.WeekEnding) AS ToDateTotal
  19.          FROM   Task AS T    INNER JOIN (
  20.                 Company AS C INNER JOIN 
  21.                 Hours AS H 
  22.                      ON C.CompanyID = H.Company) 
  23.                      ON T.TaskID = H.Task) MSQ 
  24. GROUP BY WeekEnding, 
  25.          CG, 
  26.          TG, 
  27.          ToDateTotal

Share this Question
Share on Google+
7 Replies


Expert 100+
P: 266
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
Attached Files
File Type: zip example.zip (22.6 KB, 62 views)
Aug 27 '09 #2

P: 13
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:

Expand|Select|Wrap|Line Numbers
  1. WeekEnding        Sector        Work        Weekly Total    To-Date Total
  2. 09-Aug-09        Education    Development        79            79
  3. 16-Aug-09        Education    Development        74            153
  4. 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.
Aug 27 '09 #3

Expert 100+
P: 266
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
Aug 27 '09 #4

P: 13
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)

Expand|Select|Wrap|Line Numbers
  1. SELECT  WeekEnding, 
  2.         CG, 
  3.         TG, 
  4.         SUM(Weekly) AS WeeklyTotal, 
  5.         ToDateTotal 
  6. FROM    (SELECT H.WeekEnding, 
  7.                 C.Group AS CG, 
  8.                 T.Group AS TG, 
  9.                 H.Hours AS Weekly, 
  10.                 (SELECT Sum(Hours) 
  11.                  FROM   Task    INNER JOIN (
  12.                         Company INNER JOIN 
  13.                         Hours
  14.                            ON Company.CompanyID = Hours.Company)
  15.                            ON Task.TaskID = Hours.Task 
  16.                  WHERE  Company.Group=C.Group AND 
  17.                         Task.Group=T.Group AND 
  18.                         Hours.WeekEnding<=H.WeekEnding) AS ToDateTotal
  19.          FROM   Task AS T    INNER JOIN (
  20.                 Company AS C INNER JOIN 
  21.                 Hours AS H 
  22.                      ON C.CompanyID = H.Company) 
  23.                      ON T.TaskID = H.Task) MSQ 
  24. GROUP BY WeekEnding, 
  25.          CG, 
  26.          TG, 
  27.          ToDateTotal
Aug 27 '09 #5

NeoPa
Expert Mod 15k+
P: 31,399
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!
Sep 3 '09 #6

P: 13
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.
Sep 4 '09 #7

NeoPa
Expert Mod 15k+
P: 31,399
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.
Sep 4 '09 #8

Post your reply

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