Query - Performing Calculations

[robertmeyer1]

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.

[robertmeyer1]

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.

[MMcCarthy]

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.

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;
 

Run the above as a test Robert to make sure it's operating properly before expanding and developing it.

Mary

[robertmeyer1]

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?

[MMcCarthy]

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?

[robertmeyer1]

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…. ?

[MMcCarthy]

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.

[robertmeyer1]

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.

[MMcCarthy]

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

SELECT Range, Sum(SumOfAnswer)
FROM qryGroupCalcByClient
GROUP BY Range;
 

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

SELECT Sum(SumOfSumOfAnswer)
FROM qryGroupCalcByClient
WHERE Range IN ("201-211", "212-214", "215-225");
 

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

[NeoPa]

Updated to provide name for Sum([Answer]) and also to use Between...And.

SELECT ClientID, "201-211" As Range, Sum([Answer]) AS Answers
FROM tblAnswers
WHERE QuestionNumber Between 201 And 211
GROUP BY ClientID
UNION 
SELECT ClientID, "212-214" As Range, Sum([Answer]) AS Answers
FROM tblAnswers
WHERE QuestionNumber Between 212 And 214
GROUP BY ClientID
UNION 
SELECT ClientID, "215-225" As Range, Sum([Answer]) AS Answers
FROM tblAnswers
WHERE QuestionNumber Between 215 And 225
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.

[robertmeyer1]

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?

[NeoPa]

Greedy!
Give me 5 minutes.

[NeoPa]

SELECT ClientID, "201-211" As Range, Sum([Answer]) AS Answers1, 0 As Answers2, 0 As Answers3
FROM tblAnswers
WHERE QuestionNumber Between 201 And 211
GROUP BY ClientID
UNION 
SELECT ClientID, "212-214" As Range, 0 As Answers1, Sum([Answer]) AS Answers2, 0 As Answers3
FROM tblAnswers
WHERE QuestionNumber Between 212 And 214
GROUP BY ClientID
UNION 
SELECT ClientID, "215-225" As Range, 0 As Answers1, 0 As Answers2, Sum([Answer]) AS Answers3
FROM tblAnswers
WHERE QuestionNumber Between 215 And 225
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).

[robertmeyer1]

SELECT ClientID, "201-211" As Range, Sum([Answer]) AS Answers1, 0 As Answers2, 0 As Answers3
FROM tblAnswers
WHERE QuestionNumber Between 201 And 211
GROUP BY ClientID
UNION 
SELECT ClientID, "212-214" As Range, 0 As Answers1, Sum([Answer]) AS Answers2, 0 As Answers3
FROM tblAnswers
WHERE QuestionNumber Between 212 And 214
GROUP BY ClientID
UNION 
SELECT ClientID, "215-225" As Range, 0 As Answers1, 0 As Answers2, Sum([Answer]) AS Answers3
FROM tblAnswers
WHERE QuestionNumber Between 215 And 225
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.

[robertmeyer1]

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.

[robertmeyer1]

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

[NeoPa]

No, you wouldn't.
Assuming this is a non-contiguous range then :

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

Don't forget to use the CODE tags Robert ;).

[robertmeyer1]

No, you wouldn't.
Assuming this is a non-contiguous range then :

SELECT ClientAutoID, "501 And 507" As Range, Sum([Answer]) AS OtherIncome
FROM tblAnswers
WHERE QuestionNumber In(501,507)
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”

[NeoPa]

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 :

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

But I'll check that out and confirm.

[robertmeyer1]

My bad.
I think it should have been :

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

But I'll check that out and confirm.

Nope. Still says missing operator (same as above).

[robertmeyer1]

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:

SELECT ClientID, "201-211" As Range, Sum([Answer]) AS Answers1, 0 As Answers2, 0 As Answers3
FROM tblAnswers
WHERE QuestionNumber Between 201 And 211
GROUP BY ClientID
UNION 
SELECT ClientID, "212-214" As Range, 0 As Answers1, Sum([Answer]) AS Answers2, 0 As Answers3
FROM tblAnswers
WHERE QuestionNumber Between 212 And 214
GROUP BY ClientID
UNION 
SELECT ClientID, "215-225" As Range, 0 As Answers1, 0 As Answers2, Sum([Answer]) AS Answers3
FROM tblAnswers
WHERE QuestionNumber Between 215 And 225
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?

[NeoPa]

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.

[robertmeyer1]

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.