Sub queries

[CJW]

Hi, I think my problem relates to a sub query I am trying to create.
I have created a simple course tracking database that creates a record for each course and includes the following fields (staff member responsible, course name, venue, start date, end date, course capacity) and a few more. The table containing all this information is called ‘Community’ and there is another table called ‘targets’ that holds the targets for each staff member.
Each of my team has an enrolment target for a given date range and must plan enough courses to meet that target and I want them to be able to run a query/report so that they can see their ongoing performance on a weekly basis within that date range.
I am trying to create a query that totals up the course capacity field for all of the records and then use this total in a new calculation that works out if that staff member has created enough courses.
Example:
Staff member A has created 30 course records in a given date range with the combined course capacities of 400. Their target is actually 500 for that data range so I want to calculate what % of their target has been planned i.e. 500/400*100 = 80% of planning target achieved.
I know:
• that I can create a totals row in a table but don’t know how to use that ‘result’ anywhere else
• that I can use the ‘sum’ option in the ‘total:’ row in a query but don’t know how to use that ‘result’ anywhere else
• I can make a calculation in a temporary field but I don’t know where to find the totals or sum results to use in my calculation created by using either of the above methods of adding up the values in a field column
I don’t know:
• How to use the results of the query ‘sum’ option in a new calculation in a new temporary field within the same query
I have tried doing this:-
SELECT Sum(course capacities) AS totalcapacities FROM community
And popping that in the ‘Field:’ box in the query but Access doesn’t like it one bit 
I’ve run out of ideas so if anyone could point me in the right direction, I would really appreciate it
Many thanks,
CJW

 

[jdraw]

Would be helpful to readers and to you if you would post a jpg (in ZIP format) of your tables and relationships.
You really should adopt a naming convention that does not allow embedded spaces or special characters in field and/or object names. It will save you many syntax errors and frustration.

Allen Browne subquery info/examples.
http://allenbrowne.com/subquery-01.html
http://allenbrowne.com/subquery-02.html

 

[CJW]

Would be helpful to readers and to you if you would post a jpg (in ZIP format) of your tables and relationships.
You really should adopt a naming convention that does not allow embedded spaces or special characters in field and/or object names. It will save you many syntax errors and frustration.

Allen Browne subquery info/examples.
(Cannot repost these link as only posted once)

Hi Jdraw, thanks for replying, yes I do understand about the naming conventions - I'm going to tidy all that up at some point once I've cracked this query bit There are only two tables - Community & Targets which have no relationship at the moment. I have visited the two links already thanks - still not sure if I am putting the right information in the right boxes or approaching this the right way. I'm right clicking into the 'Field:' box and selecting Zoom and entering this:

(SELECT SUM community.course capacities AS totalcapacities FROM community)

- Access is telling me it's in the wrong syntax. :-(

CJW

 

[plog]

When you use non-alpha numeric characters in field names you need to break out the brackets. Because course capacities contains a space, you need to put brackets around it in the query for it to work:

[course capacities]

Also, SUM is a function and with functions you need to enclose its arguments in parenthesis:

SUM([course capacities]) AS ...

 

[CJW]

When you use non-alpha numeric characters in field names you need to break out the brackets. Because course capacities contains a space, you need to put brackets around it in the query for it to work:

[course capacities]

Also, SUM is a function and with functions you need to enclose its arguments in parenthesis:

SUM([course capacities]) AS ...

Hi Plog, I renamed the field to remove the space and did as you suggested but I am still getting a syntax error when I try to run the query. Thanks

 

[plog]

I apologize. I skimmed your first post then saw the 2 syntaticaly errors in your 2nd post and decided to chime in. After reading it more carefully, I recommend you follow jdraws advice--post a screenshot of your relationships.

 

[CJW]

I apologize. I skimmed your first post then saw the 2 syntaticaly errors in your 2nd post and decided to chime in. After reading it more carefully, I recommend you follow jdraws advice--post a screenshot of your relationships.

Hi Plog, there aren't any relationships - just the one table (community) so I am unable to show any at the moment.

Not to worry, I'll keep trying different versions -of the same statement- even an ape learns eventually

thanks for all you help.

 

[Minty]

I don't think you can write a SQL Select expression in the Query editor field window. You are effectively adding an unlinked sub query.

As others have suggested - break it down into simple steps. Create a query to get your basic information. Create another one to get your totals. Then link those queries in a third query linking the results to the basic info.

 

[plog]

If no relationships, post 2 sets of data:

A. Starting sample data from Community. Include field names and enough sample data to cover all cases.

B. Expected result. What you expect your query to return based on the data in A.