Mardou said: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 said:Is it feasible to break the scoring routine up into several smaller
queries and execute them in series?
Duane Hookom said: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 said: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 said:Is it feasible to break the scoring routine up into several smaller
queries and execute them in series?