Help with Data Comparison: Sample VS. Population

[Megan]

Data Comparison: Sample Versus Rest of Population.

I have a population of data, and I want to take a sample of that
population and compare it against the entire population.

I have a database that records information about the types of Rulings
a person receives.
Each person works for an agency, and there are 12 agencies (see
example below).
Each person receives a ruling, and there are 2 types of rulings,
Guilty or Not Guilty. (see example below).
So, each person receives a ruling and works for an agency.

I want to compare the types of rulings an agency receives with the
types of rulings all the agencies receive.

For example:

Let's say, there were 50 total rulings.
30 = Guilty
20 = Not Guilty

Agency XYZ received 5 total Rulings: 3 Guilty, 2 Not Guilty.

Agency XYZ received:

5/50 = 10% of all of the Rulings
3/30 = 10% of all the Guilty Rulings
2/20 = 10% of all the Not Guilty Rulings

I want to compare the Rulings Agency XYZ received Versus the Rulings
all of the Agencies received, but I'm not sure of: (1) how to
retrieve and perform the proper calculations and (2) how to display
the data.

The only way I know how to do this is to create a report and group by
Type of Ruling (Guilty or Not Guilty) then by Agency, and then I can
perform the calculations, but then the report is really ugly!

Does anybody know of a better way to retrieve the data and then
display it?

For Example: Forgive me, I know this will be hard to read...

Guilty (Grouping Level 1)

Agency XYZ (Grouping Level 2)

# of Guilty Rulings = 3

Agency ABC (Grouping Level 2)

# of Guilty Rulings = 6

Total # of Guilty Rulings for All Agencies = 30 (Grouping Level 1
Footer)

Not Guilty (Grouping Level 1)

Agency XYZ (Grouping Level 2)

# of Not Guilty Rulings = 2

Agency ABC (Grouping Level 2)

# of Not Guilty Rulings = 4

Total # of Not Guilty Rulings for All Agencies = 20 (Grouping Level
1 Footer)

Total # of Rulings: Guilty + Not Guilty = 50 (Report Footer)

SIDE NOTE: I don't think I can first group by Agency then group by
Type of Decision because when you do it this way, you can't tell
whether a Ruling is Guilty or Not Guilty. At least, I haven't been
able to successfully create a report this way. I have to first group
by "type of ruling" instead of by "agency" because I don't know how
to (1) determine the type of ruling (Guilty or Not Guilty) nor (2)
count the number of rulings.
Here are the things I want to be able to do:

(1) I want to be able retrieve the total number of rulings at any
time.

(2) From the total number of Rulings, I want to know the breakdown of
those rulings, how many were guilty and how many were not guilty.

(3) Then I want a breakdown by Agency. How many Total Rulings did each
agency receive and how many were guilty or not guilty. I can do this
by creating a Crosstab Query. But what do I do with that? If my data
is always changing, can I base a report on a crosstab query?

(4) I want to be able to compare the types of rulings an agency
receives (Sample) with all of the rulings received (Total Population).
I'm not sure how to retrieve this information and then display it.
Eventually, I'd like to create a Bar Chart that compares the total
number of rulings an agency receives with the total number of rulings
received.

For example:
50
45
40
35
30 *
25 *
20 * *
15 * *
10 * * *
5 * * * *
0 * * * *
(GUILTY) (NOT GUILTY)
Agency XYZ Total Agency XYZ Total


Here is sample information about my database:

I have a table, Person, that stores information about each person.
I have a table, Case, that records information about a person's case.
I have a table, Decision, that stores the different types of decisions
a person's case can be assigned.
I have a table, Agency, that stores all of the agencies a person can
work for.
Each Agency has a Parent Agency. For example, there may be 15 Agencies
and 3 Parent Agencies.

The Parent Agencies are listed in my Agency table AND have a separate
table just for themselves called ParentAgency.

Here is a simple version of my Agency table:

AgencyID AgencyName ParentAgency
1 Jails - North 20
2 Jails - South 20
3 Jails - East 20
4 Jails - West 20
5 Hospital #1 30
6 Hospital #2 30
7 Babies 40
8 Kids 40
9 Tweens 40
20 Corrections 20
30 Health 30
40 Juvenilles 40
Here is a simple version of my ParentAgency table:

ParentAgencyID AgencyName
20 Corrections
30 Health
40 Juvenilles

Here are the types of Rulings aperson can receive:

RulingID RulingDescripti on
1 Guilty
2 Not Guilty