Thanks Duane, I currently do have a query setup that tries to get most of
my
numbers. Specifically what I have is a crosstab query with the first field
displaying the provider and it is a row heading, the second is column
heading
and a formula which calculates the total number of responses for each
question based on the yes no na:
Expr1: "Q: " & [QID] & "; " &
Choose(fdomainresponse.Answer,"Yes","No","NA")
**fdomainresponse is where the response table where the data is being
stored***
The next field is a Count field and is setup as CountOfResponse: Answer
and this is the last field in the crosstab query.
The final result is that it is laid out in such a way that the crosstab
query list the provider and then out to the side it list the total number
of
Yes, No, NA to q1, q2, etc.
From that I wanted to incorporate the Total Average percent No for all
questions from all providers so I could get an overall average no for all
providers to display at the very bottom of the report. What I did was
create
a separate query called FTotalNo and I had to break it down into a few
formulas to get the TotalPercent No for each question. So in this query it
has about 3 sets of formulas that total up the number of No responses. For
example, to get the total no I used this:
TotalNo: (NZ(Sum([Q: 1; No]),0)+NZ(Sum([Q: 2; No]),0)+NZ(Sum([Q: 3;
No]),0)+NZ(Sum([Q: 4; No]),0)+NZ(Sum([Q: 5; No]),0)+NZ(Sum([Q: 6;
No]),0)+NZ(Sum([Q: 7; No]),0)+NZ(Sum([Q: 8; No]),0)+NZ(Sum([Q: 9;
No]),0)...and so on for all 24 questions.
Then for the next field I used TotalQ9: ((NZ(Sum([q: 1;
NA]),0))+(NZ(Sum([q:
1; Yes]),0))+(NZ(Sum([q: 1; No]),0))+(NZ(Sum([q: 2; NA]),0))+(NZ(Sum([q:
2;
Yes]),0))+(NZ(Sum([q: 2; No]),0))+(NZ(Sum([q: 3; NA]),0))+(NZ(Sum([q: 3;
Yes]),0))+(NZ(Sum([q: 3; No]),0))+(NZ(Sum([q: 4; NA]),0))+(NZ(Sum([q: 4;
Yes]),0))+(NZ(Sum([q: 4; No]),0))+etc... for the responses on questions
1-9.
I then created one call TotalQ18 similar to the above to calculate the
questions from 10-18, and a third called TotalQ24 for the responses on
questions 19-24. I had to break these down into the three because the
single
formula alone was too long for the field to display and I did not know any
other way to do it.
Then I have a final field called AvgNo:
[TotalNo]/([totalq9]+[TotalQ18]+[TotalQ24]) which gets me the total
average
percent no. I then took the two querys called FTotalNo and
answersofquestionsby provider and made them into one single query that
displays the original crosstab of answersofquestions by provider and then
displays in a column the totalaverage percent no across all questions. I
was
able to get this into the report and it seemed to be working. Now the only
problem I had left was to make it so each provider showed its total
percent
No across its questions. So I tried to apply the same concept above but
the
results were strange. What happened was it listed each provider twice. The
first line was contained the totalNo across their questions with the
listing
of the number of response for each question and then the second line was a
repeat with some numbers slightly different and it showed the totalpercent
no
across all providers. In otherwords the results looked similar to this;
AvgNo TPerNo q1Y q1No q1na... and so on
prov1 57.14 2 1 1
prov1 57.14 13.5 4 1 1
prov2 57.14 2 2 3
prov2 57.14 33.33 2 2 3
and so on. Rather then just have each provider listed once it was listing
all of them twice, the first time calculating the AvgNo and the second
TPerNo. So I could not figure out how to incorporate that last
TotalPercentNo
for each individual provider with the crosstab query. It seems to be when
I
tried to combine the results of Totalpercent No for each provider with the
totalpercent no across all providers in the same query that I have the
problem. So how can I get the two together may be my problem so that I can
incorporate it easily within the report. I have a small basic database
example of what I have so far if that makes it easier to understand that I
can send if I need to. you can let me know by email at
(e-mail address removed) as to the email address to send it to.
Thanks,
Dale
Duane Hookom said:
That's why I included "Or, is this the result of a crosstab (hopefully)?"
in
my reply. I would create an expression in the query that calculates much
of
your value. For instance, you can sum all the No values for Q 1-20 with
an
expression like:
Q_1_20_No: Sum(Abs([Answer]="no" AND [Question] Between 1 And 20))
--
Duane Hookom
MS Access MVP
--
Duane,
I am not sure I know exactly what you mean by normalizing the table?
When
I
originally started this I created one way based on some things I read
throughout different forums. I then had a few suggestions from a
couple
forums that based on how I need it laid out the only way is to do a
cross
tab
query so that is the direction I took on it. The reason is because of
how
we
have to have the report laid out. We have 24 questions and each will
have
a
response of Yes, No, or NA. The report itself must be laid out in a
certain
way and here is the way they requested it:
It will have a listing of all providers down the left. Across the top
going
from left to right is a listing of all the No responses for each
question
for
each provider. Next to the number of No responses it list the %no for
that
question. At the very end of this is a total listing of all No and the
percent No. Thus the formula I pasted earlier. It is laid out similar
to
the
following: I hope this looks right on the posting
Q1 Q2 etc.. Final
Total%
from all questions
No % No %
Prov1 5 40% 1 10% long
formula
to
sort by is here
Prov2 3 20% 2 33% long
formula
to
sort by is here
Is there another way I could have done this and still be able to lay it
out
this way? My response table itself just stores the provider, the
questionid
which represents the question number, and a 1,2,or 3 based on Yes, No,
Na
response, and the date.
Thanks,
Dale
:
It may be too late but have you ever considered normalizing your table
structure? Or, is this the result of a crosstab (hopefully)?
--
Duane Hookom
MS Access MVP
I have a database and in a report I have a real long formula and I
need
to
find a way to sort by it. Specifically the formula is as follows:
=(NZ([Q: 1; No],0)+NZ([Q: 2; No],0)+NZ([Q: 3; No],0)+NZ([Q: 4;
No],0)+NZ([Q:
5; No],0)+NZ([Q: 6; No],0)+NZ([Q: 7; No],0)+NZ([Q: 8; No],0)+NZ([Q:
9;
No],0)+NZ([Q: 10; No],0)+NZ([Q: 11; No],0)+NZ([Q: 12; No],0)+NZ([Q:
13;
No],0)+NZ([Q: 14; No],0)+NZ([Q: 15; No],0)+NZ([Q: 16; No],0)+NZ([Q:
17;
No],0)+NZ([Q: 18; No],0)+NZ([Q: 19; No],0)+NZ([Q: 20; No],0)+NZ([Q:
21;
No],0)+NZ([Q: 22; No],0)+NZ([Q: 23; No],0)+NZ([Q: 24;
No],0))/(NZ([Q:
1;
Yes],0)+NZ([Q: 1; No],0)+NZ([Q: 1; NA],0)+NZ([Q: 2; Yes],0)+NZ([Q:
2;
No],0)+NZ([Q: 2; NA],0)+NZ([Q: 3; Yes],0)+NZ([Q: 3; No],0)+NZ([Q: 3;
NA],0)+NZ([Q: 4; Yes],0)+NZ([Q: 4; No],0)+NZ([Q: 4; NA],0)+NZ([Q: 5;
Yes],0)+NZ([Q: 5; No],0)+NZ([Q: 5; NA],0)+NZ([Q: 6; Yes],0)+NZ([Q:
6;
No],0)+NZ([Q: 6; NA],0)+NZ([Q: 7; Yes],0)+NZ([Q: 7; No],0)+NZ([Q: 7;
NA],0)+NZ([Q: 8; Yes],0)+NZ([Q: 8; No],0)+NZ([Q: 8; NA],0)+NZ([Q: 9;
Yes],0)+NZ([Q: 9; No],0)+NZ([Q: 9; NA],0)+NZ([Q: 10; Yes],0)+NZ([Q:
10;
No],0)+NZ([Q: 10; NA],0)+NZ([Q: 11; Yes],0)+NZ([Q: 11; No],0)+NZ([Q:
11;
NA],0)+NZ([Q: 12; Yes],0)+NZ([Q: 12; No],0)+NZ([Q: 12; NA],0)+NZ([Q:
13;
Yes],0)+NZ([Q: 13; No],0)+NZ([Q: 13; NA],0)+NZ([Q: 14;
Yes],0)+NZ([Q:
14;
No],0)+NZ([Q: 14; NA],0)+NZ([Q: 15; Yes],0)+NZ([Q: 15; No],0)+NZ([Q:
15;
NA],0)+NZ([Q: 16; Yes],0)+NZ([Q: 16; No],0)+NZ([Q: 16; NA],0)+NZ([Q:
17;
Yes],0)+NZ([Q: 17; No],0)+NZ([Q: 17; NA],0)+NZ([Q: 18;
Yes],0)+NZ([Q:
18;
No],0)+NZ([Q: 18; NA],0)+NZ([Q: 19; Yes],0)+NZ([Q: 19; No],0)+NZ([Q:
19;
NA],0)+NZ([Q: 20; Yes],0)+NZ([Q: 20; No],0)+NZ([Q: 20; NA],0)+NZ([Q:
21;
Yes],0)+NZ([Q: 21; No],0)+NZ([Q: 21; NA],0)+NZ([Q: 22;
Yes],0)+NZ([Q:
22;
No],0)+NZ([Q: 22; NA],0)+NZ([Q: 23; Yes],0)+NZ([Q: 23; No],0)+NZ([Q:
23;
NA],0)+NZ([Q: 24; Yes],0)+NZ([Q: 24; No],0)+NZ([Q: 24; NA],0))
This formula is too long to enter into the Sorting and Grouping
section
of
the report so how can I sort by this formula?
Thanks,
Dale
