Re: I am getting the error message "Query is too complex"

Responses to 36 questions should create 36 records in a table. Consider the
"At Your Survey" demo at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

The table with the list of possible response could include a field for
"weight" to provide numbers for scoring.

--
Duane Hookom
MS Access MVP


"Mardou" <Mardou@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:432970AF-C6DC-46E3-B0E1-183EFCF8AF12@xxxxxxxxxxxxxxxx
>I am not sure what you mean about executing them in series...I will copy
> below what I responded to the other wonderful person offering assistance
> and
> hopefully you can more clearly see where my problem is...
>
> the scenario is as follows:
>
> SF-36 is a general health questionnaire consisting of 36 questions. Each
> answer is scored between 1 and 6 (1,2,3,etc). The answers vary in their
> weight. For instance some of the questions are Yes/No with Yes being
> scored
> 1 and NO being scored 2. In some cases a one will be worth 50 and a two
> worth 100. In the case of six possible responses a 1 could be worth only
> 12.5 and 6 would be worth 100. These questions combine to form 8 health
> component scales. Which are an average of anywhere from 2 to 10 of the
> questions. Then the last phase is where the summary mental health(MCS)
> and
> summary physical health(PCS) scores need to be calculated. Four scale
> items
> make up each of these summary components.
>
> ok, my structure is as follows:
>
> Tables - Patient ID, Visit Date, Visit TYpe, Question 1 ...Question 36
> (look-ups to tables with the individual scoring option with one of the
> fields
> in the look-up being a value that the score is worth.)
> Queries - 1 for each of the 8 health component scores. Each query pulls
> in
> the values for the scores that make up a component. There is an
> expression
> that is a calculation that averages the items which make up the
> "Component
> average"...I am good up till this point.
>
> I tried to make a combined query which pulled the eight component averages
> in to one query and got the first "query is too complex" error. This
> didn't
> stop me as the final summary scores only consist of 4 of the scales each.
> So
> I was easily able to run queries that only consisted of 4 of the scales.
>
> This is where it gets messy: The formula to normalize the data for
> national
> levels and get my final scores is a pretty intense formula...I don't have
> it
> with me at the moment so I am going to give an example that is similar but
> not exact...to get the MCS you must create a new value for each scale
> (i.e.,
> PF_Z:=([PF*.023456]/2.1765)) This I was able to do in each of the
> respective
> queries for the scales. Then an aggregate must be created which takes each
> of
> the new values (all 8) and does something like the following:
> AGG_MENT:=(PF_Z*.23456)+(GH_Z*.34567)+(BP_Z*.45678)+(MH_Z*.56789)+(RP_Z*.98765)+(RE_Z*.87654)+(VT_Z*.76543)+(SF_Z*.54321)
> and then for the other summary
> AGG_PHYS:=(PF_Z*.23456)+(GH_Z*.34567)+(BP_Z*.45678)+(MH_Z*.56789)+(RP_Z*.98765)+(RE_Z*.87654)+(VT_Z*.76543)+(SF_Z*.54321)...if
> I can get to this point then the final steps will be to do something like
> the
> following: MCS:=(AGG_MENT+50)/10 and PCS:=(AGG_PHYS+50)/10
>
> I hope that I didn't totally confuse you. Am I asking too much of the
> program?
>
>
>
> "sean.howard" wrote:
>
>>
>> Is it feasible to break the scoring routine up into several smaller
>> queries and execute them in series?
>>
>>
>> --
>> sean.howard
>> ------------------------------------------------------------------------
>> sean.howard's Profile: http://www.msusenet.com/member.php?userid=4759
>> View this thread: http://www.msusenet.com/t-1871023284
>>
>>


.

Relevant Pages

  • Re: I am getting the error message "Query is too complex"
    ... "Duane Hookom" wrote: ... > Responses to 36 questions should create 36 records in a table. ... >> stop me as the final summary scores only consist of 4 of the scales each. ... >> I was easily able to run queries that only consisted of 4 of the scales. ...
    (microsoft.public.access.queries)
  • Re: Forms and Queries
    ... But it sounds like reading about crosstab ... queries might help you out. ... > Yes, No or N/A responses. ... The query will not summarize the data the way I would like. ...
    (microsoft.public.access.formscoding)
  • Re: Automating Macro Responses
    ... Queries can run faster or slower depending on a number of factors... ... Does the query use any Access functions on data coming from linked sources ... The sequence is the same and the responses are always ... Delete tbl_Query (prompt appears confirming that I want to do this) ...
    (microsoft.public.access.queries)
  • Re: I am getting the error message "Query is too complex"
    ... I am not sure what you mean about executing them in series...I will copy ... In some cases a one will be worth 50 and a two ... In the case of six possible responses a 1 could be worth only ... stop me as the final summary scores only consist of 4 of the scales each. ...
    (microsoft.public.access.queries)
  • Re: Form and SQL Help please
    ... I'm a bit worried about the fact that you have a field named "question1". ... Questions and Responses ... You can then create a single GROUP BY or crosstab query to return the total ... The queries I need to perform in one go are: ...
    (microsoft.public.access.formscoding)