Getting Syntax Error whilst applying Totals in Query

[Yesurbius]

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)

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.

[ajalwaysus]

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

[Yesurbius]

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.

[ajalwaysus]

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

[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

[NeoPa]

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!

[Yesurbius]

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.

[NeoPa]

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.