Too Complex Error in Main and SubReport

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I don't understand how to correct this problem.
I doing a training database and in the evaluation for question 1.

The user can select Excellent = 1, Good=2, Fair=3.
I must do an average on question 1 and then a percentage.
(I have control source fields(textboxes) with
=Avg(IIf([qryLTALLEVAL]![Q1]=1,1,0)) in the Excellent
column,=Avg(IIf([qryLTALLEVAL]![Q1]=2,1,0)) in the Good column and
=Avg(IIf([qryLTALLEVAL]![Q1]=3,1,0)) in the Fair column. The qryLTALLEVAL is
the query by question. I have about 15 questions total. The textbox format =
Pecentage and Decimal Places=0. The report works for 3 responses and I
thought I was home free until the customer added 30 responses and now the
report fails with the too complex error. I tried to move to a calculated
query but the code did not work in all cases.

Thank you in advance for any guidance.

R. Robinson bka "Rocky"
 
Thank you, Duane

I have changed the design of my table based on your example, however I'm now
confused as to how to reference a particular variable.

Table design before was :

EvalClassID Q1
14 1
14 2

Now it's :
EvalClassID Quest_ID Resp Number of Responses
14 1 1 2

So how do I reference one record(just where question id is 1 and resp=1,
display the number of responses) versus all records within the crosstab query?

Thank you,
Racquel

Duane Hookom said:
Is it too late to change your table structure? You might want to review "At
Your Survey" at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP


Rocky said:
I don't understand how to correct this problem.
I doing a training database and in the evaluation for question 1.

The user can select Excellent = 1, Good=2, Fair=3.
I must do an average on question 1 and then a percentage.
(I have control source fields(textboxes) with
=Avg(IIf([qryLTALLEVAL]![Q1]=1,1,0)) in the Excellent
column,=Avg(IIf([qryLTALLEVAL]![Q1]=2,1,0)) in the Good column and
=Avg(IIf([qryLTALLEVAL]![Q1]=3,1,0)) in the Fair column. The qryLTALLEVAL is
the query by question. I have about 15 questions total. The textbox format =
Pecentage and Decimal Places=0. The report works for 3 responses and I
thought I was home free until the customer added 30 responses and now the
report fails with the too complex error. I tried to move to a calculated
query but the code did not work in all cases.

Thank you in advance for any guidance.

R. Robinson bka "Rocky"
Click to expand...
Click to expand...
 
Select Quest_ID, Resp, Count(Resp)
FROM tblYourTable
WHERE Quest_ID=1 and Resp=1
GROUP BY Quest_ID, Resp

--
Duane Hookom
MS Access MVP
--

Rocky said:
Thank you, Duane

I have changed the design of my table based on your example, however I'm
now
confused as to how to reference a particular variable.

Table design before was :

EvalClassID Q1
14 1
14 2

Now it's :
EvalClassID Quest_ID Resp Number of Responses
14 1 1 2

So how do I reference one record(just where question id is 1 and resp=1,
display the number of responses) versus all records within the crosstab
query?

Thank you,
Racquel

Duane Hookom said:
Is it too late to change your table structure? You might want to review
"At
Your Survey" at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP


Rocky said:
I don't understand how to correct this problem.
I doing a training database and in the evaluation for question 1.

The user can select Excellent = 1, Good=2, Fair=3.
I must do an average on question 1 and then a percentage.
(I have control source fields(textboxes) with
=Avg(IIf([qryLTALLEVAL]![Q1]=1,1,0)) in the Excellent
column,=Avg(IIf([qryLTALLEVAL]![Q1]=2,1,0)) in the Good column and
=Avg(IIf([qryLTALLEVAL]![Q1]=3,1,0)) in the Fair column. The
qryLTALLEVAL is
the query by question. I have about 15 questions total. The textbox
format =
Pecentage and Decimal Places=0. The report works for 3 responses and I
thought I was home free until the customer added 30 responses and now
the
report fails with the too complex error. I tried to move to a
calculated
query but the code did not work in all cases.

Thank you in advance for any guidance.

R. Robinson bka "Rocky"
Click to expand...
Click to expand...
Click to expand...
 
Thank you :)


Duane Hookom said:
Select Quest_ID, Resp, Count(Resp)
FROM tblYourTable
WHERE Quest_ID=1 and Resp=1
GROUP BY Quest_ID, Resp

--
Duane Hookom
MS Access MVP
--

Rocky said:
Thank you, Duane

I have changed the design of my table based on your example, however I'm
now
confused as to how to reference a particular variable.

Table design before was :

EvalClassID Q1
14 1
14 2

Now it's :
EvalClassID Quest_ID Resp Number of Responses
14 1 1 2

So how do I reference one record(just where question id is 1 and resp=1,
display the number of responses) versus all records within the crosstab
query?

Thank you,
Racquel

Duane Hookom said:
Is it too late to change your table structure? You might want to review
"At
Your Survey" at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP


I don't understand how to correct this problem.
I doing a training database and in the evaluation for question 1.

The user can select Excellent = 1, Good=2, Fair=3.
I must do an average on question 1 and then a percentage.
(I have control source fields(textboxes) with
=Avg(IIf([qryLTALLEVAL]![Q1]=1,1,0)) in the Excellent
column,=Avg(IIf([qryLTALLEVAL]![Q1]=2,1,0)) in the Good column and
=Avg(IIf([qryLTALLEVAL]![Q1]=3,1,0)) in the Fair column. The
qryLTALLEVAL
is
the query by question. I have about 15 questions total. The textbox
format
=
Pecentage and Decimal Places=0. The report works for 3 responses and I
thought I was home free until the customer added 30 responses and now
the
report fails with the too complex error. I tried to move to a
calculated
query but the code did not work in all cases.

Thank you in advance for any guidance.

R. Robinson bka "Rocky"
Click to expand...
Click to expand...
Click to expand...
 
Back
Top