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.
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
7 2828
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
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.
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
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 32,556
Expert Mod 16PB
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!
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 32,556
Expert Mod 16PB
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Sondra Wilson |
last post by:
I have created a sorted query. The total(records) I get in the query is
correct. My problem lies with getting that total into my report.
I use count to insert the total number of records in my...
|
by: Dalan |
last post by:
I can't seem to find a workaround of Query Syntax Error. Actually, the
query performs just fine, except when the last record on a related
subform is deleted, then it generates a Runtime Error 3075...
|
by: Dalan |
last post by:
I'm experiencing a Query Syntax Error with an Access 97 Db. Actually,
the query performs as expected when adding any new records or editing
existing ones and even deleting records, EXCEPT when the...
|
by: dd_bdlm |
last post by:
Please help this one is driving me mad! I have searched and read all
the topics on the error message I am receiving but none seem to apply
to me!
I have quite a complex query linking all parts...
|
by: Mark Carlyle via AccessMonster.com |
last post by:
I have this update query that I am trying to run. I know the syntax is
messed up but do not know how to correct it.
Select 'UPDATE', Transactions,'Set = where =
' From "Get Daily Balances"
...
|
by: New Guy |
last post by:
I'm trying to work with a system that somebody else built and I am
confounded by the following problem:
There is a table of payments and a table of charges. Each client has
charges and payments...
|
by: Gilberto |
last post by:
Hello,
This is VERY basic but i havent do it before.
I have a form that is missing to record in its table one record, as the record displayed when i run the form its just a "=DLookUp("","") so...
|
by: srusskinyon |
last post by:
I need some help getting unique records from our database! I work for
a small non-profit homeless shelter. We keep track of guest
information as well as what services we have offered for...
|
by: raghuvendra |
last post by:
Hi
I have a jsp page with 4 columns: namely Category name , Category order, Input field and a submit button.
All these are aligned in a row. And Each Category Name has its corresponding Category...
|
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: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |