Access calculations in report

[Chris Morrison]

I'll try again....
I have a table called safety that has a column/field
called results, that has numbers in it from 0 thru 5. I
need to design a report that will count the number of
occurances for each 0 thru 5 answer, and a frequency
distribution of what % of outcomes there were - for
example - what % of the totals were 5,s, 4's, 3's, etc...
How can I do this as a report, and make it work? I
can do a total count for all records, and a total average
score for all the records, but I am absolutely stuck on
these other calculations.

I would like to have it look like this in the end (or
footer, or aggregate?) section of the report. I have
always had trouble using a cross tab report, I never can
get it to come out right, and I dont know if that is the
answer because I have to list a series of names in the
detail section of the report as well.


safety plan score count % of total
5 1 25.0
4 0 0.0
3 1 25.0
2 2 50.0
1 0 0.0
0 0 0.0
I hope this adequately explains the situation


This report is based on a query for the safety table that
runs for a user defined timeframe. Thanks for any
help/assistance someone can give me, it is greatly
appreciated.


..

 

[Marshall Barton]

I'll try again....
I have a table called safety that has a column/field
called results, that has numbers in it from 0 thru 5. I
need to design a report that will count the number of
occurances for each 0 thru 5 answer, and a frequency
distribution of what % of outcomes there were - for
example - what % of the totals were 5,s, 4's, 3's, etc...
How can I do this as a report, and make it work? I
can do a total count for all records, and a total average
score for all the records, but I am absolutely stuck on
these other calculations.

I would like to have it look like this in the end (or
footer, or aggregate?) section of the report. I have
always had trouble using a cross tab report, I never can
get it to come out right, and I dont know if that is the
answer because I have to list a series of names in the
detail section of the report as well.


safety plan score count % of total
5 1 25.0
4 0 0.0
3 1 25.0
2 2 50.0
1 0 0.0
0 0 0.0
I hope this adequately explains the situation


This report is based on a query for the safety table that
runs for a user defined timeframe. Thanks for any
help/assistance someone can give me, it is greatly
appreciated.

THe secret to most any report is to create a query that
organizes the data pretty closely to what the report is
supposed to present. In this case, it sounds like you want
to use a totals query base on your existing query.

 

[Stacey]

I have no advice for you, but I am struggling with the
exact same problem. If you get any good advice on how to
do this, I would love to know what it is. You did a great
job explaining what you are trying to do. I read the
other response to your message and tried to do a total
field within the query, but it only counted the total
number of enteries for the field, it did not separately
count each response. Let me know if you find anything
out. Thanks.

 

[Marshall Barton]

I have no advice for you, but I am struggling with the
exact same problem. If you get any good advice on how to
do this, I would love to know what it is. You did a great
job explaining what you are trying to do. I read the
other response to your message and tried to do a total
field within the query, but it only counted the total
number of enteries for the field, it did not separately
count each response.

Stacey, a Totals type query uses a Group By on some fields
and an aggregate function on others (Count, Sum, Avg, etc).

To be more specific, I'd need to know more about the data
structure you're using.

For example, let's assume Chris's existing query provided a
separate record for each result (like what he has in his
table). To get a count of zero for results that do not
appear in the table (e.g. 1 and 4), you have to have a table
with a record for each possible result (e.g. 1, 2, 3, 4, 5)
and use an Outer Join to the table/query.

A query that counted each separate result value would then
be something along these lines:

SELECT Result, Count(*) As CountOfResult
FROM thetable LEFT JOIN allresultstable
ON thetable.Result = allresultstable.Result
GROUP BY Result

This will provide the report with records like:
1 0
2 2
3 1
4 0
5 1

Then the report can calculate the total number of responses
in a text box (name it txtTotalResult) in the report header
with the expression
=Sum(CountOfResult)
and the precentage of the total would be in another detail
text box with the expression
=CountOfResult / txtTotalResult

 

[Guest]

I had to do this same thing with one of my reports. This
is what worked for me.
For where you want to count the number of 5's, 4's, etc...
I used the formula:
=Abs(Sum([Your Query Name]![Your Field] In ("1")))
This would sum all of the 1's I had.

After you do this for each of the numbers, you should
have a field that totals all of the answers. To show the
percentages. Create another text box and create a
formula that divides (Sum of 1's)/(Total) You should be
plugging in whatever name of the text box that you want
to divide is. For example, one of mine looks like this:
=[Text22]/[Text31]

This will give you a decimal, to show it as a
percentage...in the properties box, click on the Format
tab and then change the format to percent. You can then
add however many decimal places you would like.