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

Query - Performing Calculations

P: 99
Hi. I have been working on creating the structure of a DB and now I am trying to create some queries which will perform calculations. I have 1 table, tblAnswers which is based off an append query. tblAnswers contains a list of prepopulated questions and corresponding answers (numeric) which are filled in on the forms.

So tblAnswers has 4 fields: AnswerID (autonumber); ClientID; QuestionNumber; and Answer. ClientID is used to relate the questions/answers to each client.

The query I am going to make needs to calculate answers per client. So I understand to insert the ClientID into the query. My issue arises with the QuestionNumber and Answer fields.

I am going to need to perform calculation on specific question groups. For example: QuestionNumber 201-211 will need to be added together. The added answer would be displayed in the query. I need to do calculations like this about 50-100 times. Each with different groupings (201-211;212-214;215-225…).

How do I set the query up to allow for this? Do I need to do this in the criteria or QuestionNumber? Or do I need to insert Answers multiple times into the query and try to distinguish each one as a grouping (if so, how?). Thanks in advance.
Dec 12 '06 #1
Share this Question
Share on Google+
22 Replies


P: 99
I got a suggestion to simply have a "Group" predefined for each question. This works for some of the queries I will need to run, but others will require interactions between "groups" so I'm not sure this will work. If anyone else has thougths let me know. Thanks.
Dec 12 '06 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
Hi. I have been working on creating the structure of a DB and now I am trying to create some queries which will perform calculations. I have 1 table, tblAnswers which is based off an append query. tblAnswers contains a list of prepopulated questions and corresponding answers (numeric) which are filled in on the forms.

So tblAnswers has 4 fields: AnswerID (autonumber); ClientID; QuestionNumber; and Answer. ClientID is used to relate the questions/answers to each client.

The query I am going to make needs to calculate answers per client. So I understand to insert the ClientID into the query. My issue arises with the QuestionNumber and Answer fields.

I am going to need to perform calculation on specific question groups. For example: QuestionNumber 201-211 will need to be added together. The added answer would be displayed in the query. I need to do calculations like this about 50-100 times. Each with different groupings (201-211;212-214;215-225…).

How do I set the query up to allow for this? Do I need to do this in the criteria or QuestionNumber? Or do I need to insert Answers multiple times into the query and try to distinguish each one as a grouping (if so, how?). Thanks in advance.
You will probably need to create a union query based on a series of grouping queries something like the following Robert.

Expand|Select|Wrap|Line Numbers
  1. SELECT ClientID, "201-211" As Range, Sum([Answer])
  2. FROM tblAnswers
  3. WHERE QuestionNumber>=201 AND QuestionNumber<=211
  4. UNION 
  5. SELECT ClientID, "212-214" As Range, Sum([Answer])
  6. FROM tblAnswers
  7. WHERE QuestionNumber>=212 AND QuestionNumber<=214
  8. UNION
  9. SELECT ClientID, "215-225" As Range, Sum([Answer])
  10. FROM tblAnswers
  11. WHERE QuestionNumber>=215 AND QuestionNumber<=225
  12. GROUP BY ClientID;
  13.  
Run the above as a test Robert to make sure it's operating properly before expanding and developing it.

Mary
Dec 12 '06 #3

P: 99
Mary, I ran into a problem using the code:

Error Message:

“You tried to run a query which does not include the specified expression “ClientID” as part of an aggregate function.” (Error 3122).

You did not enter an aggregate function in the TRANSFORM statement.

Same code as above. I also tried changing the ClientID (after select) to QuestionNumber but the same error results. The ClientID field is anutonumber starting at 1 in tblClients and is a FK in tblAnswers. Any thoughts?
Dec 13 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
Did you include the last line.

GROUP BY ClientID;


Mary, I ran into a problem using the code:

Error Message:

“You tried to run a query which does not include the specified expression “ClientID” as part of an aggregate function.” (Error 3122).

You did not enter an aggregate function in the TRANSFORM statement.

Same code as above. I also tried changing the ClientID (after select) to QuestionNumber but the same error results. The ClientID field is anutonumber starting at 1 in tblClients and is a FK in tblAnswers. Any thoughts?
Dec 13 '06 #5

P: 99
Did you include the last line.

GROUP BY ClientID;
Yea,

SELECT ClientID, "201-211" As Range, Sum([Answer])
FROM tblAnswers
WHERE QuestionNumber>=201 AND QuestionNumber<=211
UNION
SELECT ClientID, "212-214" As Range, Sum([Answer])
FROM tblAnswers
WHERE QuestionNumber>=212 AND QuestionNumber<=214
UNION
SELECT ClientID, "215-225" As Range, Sum([Answer])
FROM tblAnswers
WHERE QuestionNumber>=215 AND QuestionNumber<=225
GROUP BY ClientID;

Could the fact that ClientID is an autonumber be affecting this? tblAnswers shows 1 line for each questionnumber. So it has clientID 1; questionnumber 201, answer; then clientID 1; questionnumber 202, answer…. ?
Dec 13 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
Sorry Robert,

You'll need to put the group by line after each statement.

Mary

SELECT ClientID, "201-211" As Range, Sum([Answer])
FROM tblAnswers
WHERE QuestionNumber>=201 AND QuestionNumber<=211
GROUP BY ClientID
UNION
SELECT ClientID, "212-214" As Range, Sum([Answer])
FROM tblAnswers
WHERE QuestionNumber>=212 AND QuestionNumber<=214
GROUP BY ClientID
UNION
SELECT ClientID, "215-225" As Range, Sum([Answer])
FROM tblAnswers
WHERE QuestionNumber>=215 AND QuestionNumber<=225
GROUP BY ClientID;

Could the fact that ClientID is an autonumber be affecting this? tblAnswers shows 1 line for each questionnumber. So it has clientID 1; questionnumber 201, answer; then clientID 1; questionnumber 202, answer…. ?
No, this is fine.
Dec 13 '06 #7

P: 99
Awesome. That works great. Thanks Mary.

Now for 2 follow ups.

1. I cannot view the design view for this query. Is their a way to change the name of the field with the answers? The title right now is Exp1002 and I thought I would rename it something I could remember better.


2. Now for the slightly more complicated questions. I am going to be performing calculations like this for all my question #’s. (i.e. grouping them together in some fashion). After this, I am going to have to take some of the specific grouping and run further calculations. For example: I will have to perform a calculation which will “sum” all the question numbers 201-211, 212-214, 215-225; I plan on doing that by basing the information off this query and doing a “sum” without including the clientID. Think this will work ok.

2b. Also, I am going to have to eventually add those summed numbers without the clientId together. So eventually numbers 201-225 will all be added together to display a single answer. Thanks.

2c. How do I set these up to be included on a report? Create a report based off query, then insert the field Expr1002. How can I make it show just question numbers 201-211, 212-214 … for the display on the report?

Thanks for the help.
Dec 14 '06 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
Awesome. That works great. Thanks Mary.

Now for 2 follow ups.

1. I cannot view the design view for this query. Is their a way to change the name of the field with the answers? The title right now is Exp1002 and I thought I would rename it something I could remember better.
Union queries can't be represented in Access design view. If you think about it you'll see why.

2. Now for the slightly more complicated questions. I am going to be performing calculations like this for all my question #’s. (i.e. grouping them together in some fashion). After this, I am going to have to take some of the specific grouping and run further calculations. For example: I will have to perform a calculation which will “sum” all the question numbers 201-211, 212-214, 215-225; I plan on doing that by basing the information off this query and doing a “sum” without including the clientID. Think this will work ok.
I would guess so. It would be a little complicated (surprise!) but I think it's manageable.

e.g. if we call the union query qryGroupCalcByClient

Expand|Select|Wrap|Line Numbers
  1. SELECT Range, Sum(SumOfAnswer)
  2. FROM qryGroupCalcByClient
  3. GROUP BY Range;
  4.  
This would give you the sum for each range.

Mary

2b. Also, I am going to have to eventually add those summed numbers without the clientId together. So eventually numbers 201-225 will all be added together to display a single answer. Thanks.
Something like

Expand|Select|Wrap|Line Numbers
  1. SELECT Sum(SumOfSumOfAnswer)
  2. FROM qryGroupCalcByClient
  3. WHERE Range IN ("201-211", "212-214", "215-225");
  4.  

2c. How do I set these up to be included on a report? Create a report based off query, then insert the field Expr1002. How can I make it show just question numbers 201-211, 212-214 … for the display on the report?
We'll get to this later once we see the final query.

Mary
Dec 14 '06 #9

NeoPa
Expert Mod 15k+
P: 31,661
Updated to provide name for Sum([Answer]) and also to use Between...And.
Expand|Select|Wrap|Line Numbers
  1. SELECT ClientID, "201-211" As Range, Sum([Answer]) AS Answers
  2. FROM tblAnswers
  3. WHERE QuestionNumber Between 201 And 211
  4. GROUP BY ClientID
  5. UNION 
  6. SELECT ClientID, "212-214" As Range, Sum([Answer]) AS Answers
  7. FROM tblAnswers
  8. WHERE QuestionNumber Between 212 And 214
  9. GROUP BY ClientID
  10. UNION 
  11. SELECT ClientID, "215-225" As Range, Sum([Answer]) AS Answers
  12. FROM tblAnswers
  13. WHERE QuestionNumber Between 215 And 225
  14. GROUP BY ClientID;
In fact, for a union query you technically only need the names in the first SELECT query.
Don't ever do that though, as it is more difficult to maintain later.
Dec 14 '06 #10

P: 99
Cool Deal. So If I change the title parts:

SELECT ClientID, "201-211" As Range, Sum([Answer]) AS Answers

SELECT ClientID, "212-214" As Range, Sum([Answer]) AS Answers

To

SELECT ClientID, "201-211" As Range, Sum([Answer]) AS Answers1

SELECT ClientID, "212-214" As Range, Sum([Answer]) AS Answers2

It only displays the Answers1. Is it possible to break it out, then I could have each “Answer” as a field in the query and it may be easier to use further in that way. What do ya’ll think?
Dec 14 '06 #11

NeoPa
Expert Mod 15k+
P: 31,661
Greedy!
Give me 5 minutes.
Dec 14 '06 #12

NeoPa
Expert Mod 15k+
P: 31,661
Expand|Select|Wrap|Line Numbers
  1. SELECT ClientID, "201-211" As Range, Sum([Answer]) AS Answers1, 0 As Answers2, 0 As Answers3
  2. FROM tblAnswers
  3. WHERE QuestionNumber Between 201 And 211
  4. GROUP BY ClientID
  5. UNION 
  6. SELECT ClientID, "212-214" As Range, 0 As Answers1, Sum([Answer]) AS Answers2, 0 As Answers3
  7. FROM tblAnswers
  8. WHERE QuestionNumber Between 212 And 214
  9. GROUP BY ClientID
  10. UNION 
  11. SELECT ClientID, "215-225" As Range, 0 As Answers1, 0 As Answers2, Sum([Answer]) AS Answers3
  12. FROM tblAnswers
  13. WHERE QuestionNumber Between 215 And 225
  14. GROUP BY ClientID;
This query can be used as the source for another query which could be GROUPed to link them on a single line if required (Using Sum(Answers1) etc).
Dec 14 '06 #13

P: 99
Expand|Select|Wrap|Line Numbers
  1. SELECT ClientID, "201-211" As Range, Sum([Answer]) AS Answers1, 0 As Answers2, 0 As Answers3
  2. FROM tblAnswers
  3. WHERE QuestionNumber Between 201 And 211
  4. GROUP BY ClientID
  5. UNION 
  6. SELECT ClientID, "212-214" As Range, 0 As Answers1, Sum([Answer]) AS Answers2, 0 As Answers3
  7. FROM tblAnswers
  8. WHERE QuestionNumber Between 212 And 214
  9. GROUP BY ClientID
  10. UNION 
  11. SELECT ClientID, "215-225" As Range, 0 As Answers1, 0 As Answers2, Sum([Answer]) AS Answers3
  12. FROM tblAnswers
  13. WHERE QuestionNumber Between 215 And 225
  14. GROUP BY ClientID;
This query can be used as the source for another query which could be GROUPed to link them on a single line if required (Using Sum(Answers1) etc).
Sweet. I’ll try to wait about 6 minutes in the future…

For this code, I followed and tried it out. Works great. I am trying on the 3 question ranges we have been discussing and when combining it in a new qry I get 3 fields (awesome) in a 9x9 grid. So I have 1 of 3 fields filled in w/ the amount. Is it possible to adjust this to only allow for the amount filled in for each field? That is, if Answe1 row 1 has a value, rows 2 & 3 are currently 0 (Can we make them simply not display or to disappear altogether? Thanks.
Dec 14 '06 #14

P: 99
New follow-up question, to go with my last. So I started working on making the union queries – grouping etc… I created about 10 such queries right now. Following the code (they work). Should I combine these queries into 1? Is that a problem (it would be a lot of code). And there would be a lot of answer/fields used since for every client there are about 75 fields. So maybe at max 2000 clients x 75 = 150,000 records? If I do leave them separate (separation based on question types) can I include the union queries in other queries? As discusses above I am going to need to sum them without clientID’s and then I will eventually need to add the summed fields and subtract certain ones from the others. So what is a good way to start planning that? Thanks guys for the help. I’ve finally got a DB that is structured properly… I’m so happy.
Dec 14 '06 #15

P: 99
Whoops: Add on:

I have 1 set of questionsnumbers that need to be combined which aren't in sequential order. It is basically (ex: ) 501 & 507.

How should that look in the code:

SELECT ClientAutoID, "501 And 507" As Range, Sum([Answer]) AS OtherIncome
FROM tblAnswers
WHERE QuestionNumber 501 And 507
GROUP BY ClientAutoID;

No luck when I tried this
Dec 14 '06 #16

NeoPa
Expert Mod 15k+
P: 31,661
No, you wouldn't.
Assuming this is a non-contiguous range then :
Expand|Select|Wrap|Line Numbers
  1. SELECT ClientAutoID, "501 And 507" As Range, Sum([Answer]) AS OtherIncome
  2. FROM tblAnswers
  3. WHERE QuestionNumber In(501,507)
  4. GROUP BY ClientAutoID;
Don't forget to use the CODE tags Robert ;).
Dec 14 '06 #17

P: 99
No, you wouldn't.
Assuming this is a non-contiguous range then :
Expand|Select|Wrap|Line Numbers
  1. SELECT ClientAutoID, "501 And 507" As Range, Sum([Answer]) AS OtherIncome
  2. FROM tblAnswers
  3. WHERE QuestionNumber In(501,507)
  4. GROUP BY ClientAutoID;
Don't forget to use the CODE tags Robert ;).
Ok I used the above code, but got the following error message:

Syntax Error (missing operator) in query expression “501 And 507”
Dec 18 '06 #18

NeoPa
Expert Mod 15k+
P: 31,661
Ok I used the above code, but got the following error message:

Syntax Error (missing operator) in query expression “501 And 507”
My bad.
I think it should have been :
Expand|Select|Wrap|Line Numbers
  1. SELECT ClientAutoID, "501 And 507" As Range, Sum([Answer]) AS OtherIncome
  2. FROM tblAnswers
  3. WHERE QuestionNumber In(501,507)
  4. GROUP BY ClientAutoID, "501 And 507";
But I'll check that out and confirm.
Dec 18 '06 #19

P: 99
My bad.
I think it should have been :
Expand|Select|Wrap|Line Numbers
  1. SELECT ClientAutoID, "501 And 507" As Range, Sum([Answer]) AS OtherIncome
  2. FROM tblAnswers
  3. WHERE QuestionNumber In(501,507)
  4. GROUP BY ClientAutoID, "501 And 507";
But I'll check that out and confirm.
Nope. Still says missing operator (same as above).
Dec 18 '06 #20

P: 99
Ok. I have the first queries pretty much finished. (Except for the previous post issue: 501 & 507). Now I am beginning on a second query for each (1st query) to do as we discussed above:

Expand|Select|Wrap|Line Numbers
  1. SELECT ClientID, "201-211" As Range, Sum([Answer]) AS Answers1, 0 As Answers2, 0 As Answers3
  2. FROM tblAnswers
  3. WHERE QuestionNumber Between 201 And 211
  4. GROUP BY ClientID
  5. UNION 
  6. SELECT ClientID, "212-214" As Range, 0 As Answers1, Sum([Answer]) AS Answers2, 0 As Answers3
  7. FROM tblAnswers
  8. WHERE QuestionNumber Between 212 And 214
  9. GROUP BY ClientID
  10. UNION 
  11. SELECT ClientID, "215-225" As Range, 0 As Answers1, 0 As Answers2, Sum([Answer]) AS Answers3
  12. FROM tblAnswers
  13. WHERE QuestionNumber Between 215 And 225
  14. GROUP BY ClientID;
This query can be used as the source for another query which could be GROUPed to link them on a single line if required (Using Sum(Answers1) etc).
I run into a problem with this method though. The largest query group I have has 13 groupings. (ex: 301-305, 306-311, 312-320….. 397-399). So I tried following the coded example above, and inserted the As Answers1,2,3…13 with correct naming. When I try to run the query, the whole thing goes bust. Basically, it pops up an error message and the DB must be closed. I figured the query must be too complex to run this.

Any thoughts on how to do the above in a manageable method?
Dec 18 '06 #21

NeoPa
Expert Mod 15k+
P: 31,661
1. Let's try to do one thing at a time. You have access to all your info but I haven't. It's complicated enough just dealing with one issue.

2. If you have a problem with a query you must post the SQL and the error message if you want any help.

3. No-one ever said this was easy. A more manageable method might be to hire an expert.
No, this is fundamental to database work - Databases can only be so powerful by treating records as individual 'similar' objects which can be processed en masse. Sure you can do something in code, but that will probably be even harder for you.

To get back to the 'current' issue :
I really can't see why your SQL would have any problem. Can you either - post the exact (copy/paste) SQL you are using as well as the exact error message or - PM me a link to a download of your database (if appropriate) so that I can look at it in more detail. There would need to be some data available which demonstrates the problems.
Dec 19 '06 #22

P: 99

3. No-one ever said this was easy. A more manageable method might be to hire an expert.
I think I’ll try going through my company some more. The IT department is pretty helpful, but they are also busy. I’ll let you know what comes of it and if I need more help. I’ll continue posting on other issues so I can keep working on the DB. Thanks for all the help, I really appreciate it.
Dec 19 '06 #23

Post your reply

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