468,777 Members | 2,364 Online

# Help with Data Comparison: Sample VS. Population

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
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.

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
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

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 RulingDescription
1 Guilty
2 Not Guilty
Nov 13 '05 #1
0 3547

### This discussion thread is closed

Replies have been disabled for this discussion.