Grouping in a Report & Calculating Percentages

[Megan]

quick summary: i'm having problems trying to group fields in a report
in order to calculate percentages. to calculate percentages, i'm
comparing the results from my grouped fields to the totals.

first, let me say that this is a really long post. i wasn't sure how
much information/ background to provide, so i thought more was better
than less. i tried to delineate certain areas so that it would be
easy to peruse my posting and find the info you're looking for. the
first two sections describe the situation and the reports i'm trying
to create. the remaining sections deal with the relationships and
structure of the db, which isn't necessary for you to read.

here is an outline of my post:

I. Background data for the Reports i'm trying to create
II. Ideas for creating the reports
III. Background
IV. Examples
V. Table Structure
VI. Examples of values in Issue Table and Decision Table

.................................................. .....................................
I. Background data for the Reports i'm trying to create
.................................................. .....................................

let's say i have 3 lawyers: bieber, foster, and marks. to keep the
division simple and easy, let's say we have 15 cases. since a case
can have 1 or more issues and 1 or more decisions, let's say we have
9 cases that each has 2 issues and 2 decisions. the remaining 6 cases
have 1 issue and 1 decision each. so, we have:

9 of the 15 cases have 2 issues and 2 decisions
6 of the 15 cases have 1 issue and 1 decision

to get the total number of issues:
9 cases x 2 issues = 18 issues
6 cases x 1 issue = 6 issues
total number of issues = 18 + 6 = 24 issues

NOTE: the same goes for decisions.

to get the total number of decisions:
9 cases x 2 decisions = 18 decisions
6 cases x 1 decision = 6 issues
total number of decisions = 18 + 6 = 24 decisions

so, it all boils down to this:

3 lawyers
15 total cases
24 issues
24 decisions
.................................................. ..............................
II. Ideas for creating the reports
.................................................. ..............................

i want to compare the decisions the lawyers get.

of the 24 total decisions:
8 = not guilty
5 = guilty
11 = pleaded out

bieber = 2 not guilty, 2 guilty, and 4 pleaded out.
foster = 6 not guilty, 1 guilty, and 3 pleaded out.
marks = 0 not guilty, 2 guilty, and 4 pleaded out.

i want to create a report that groups the lawyers and groups the
decisions that lawyer has received.

then i want to compare the decisions each lawyer gets to the total
number of decisions in order to get percentages.

for example, i want to calculate 2 types of percentages for each
lawyer.

1.) bieber = 2 not guilty, 2 guilty, and 4 pleaded out.

therefore:

bieber = 2/8 not guilty, 2/5 guilty, and 5/11 pleaded out.

2.) bieber = 2 not guilty, 2 guilty, and 4 pleaded out.

therefore: 2 + 2 + 4 = 8 total decisions for bieber. so, bieber got
8/24 (24 being the total number of decisions)
how i did it: i grouped by decision, then by lawyer name, but the
report turns out ugly. i ran a crosstab query and that turned out
nicely, but i don't think i can use a crosstab query for a report.

does anyone have any suggestions on how to format/ group a report like
this? thanks in advance!

.................................................. ..............................
III. Background
.................................................. ..............................

i have a court rulings database that records information about
grievants and their cases. the db records personal information about
grievants, such as social security number, first name, last name,
address, etc...

i have a 1 to many relationship between grievant and case:

each grievant can have 1 or more cases.
each case can have 1 and only 1 grievant.

the table, case, records information about a grievant's case, such as
caseid, the grievant's social security number, case contact date,
decision date, etc...

each case can have 1 or more issues, and each case can have 1 or more
decisions. the table, case, has 2 many to many relationships.

case has a many to many relationship with issue, and case has a many
to many relationship with decision.

therefore, i created a single bridge table between case and issue and
between case and decision. this bridge table, caseresults, has 4
fields:

id: the primary key is an autonumber
caseid: for the case
issueid: for the issue
decisionid: for the decision
issue
/
case - caseresults
\
decision


.................................................. ..............................
IV. here are 2 examples of what might happen:
.................................................. ..............................

in the 1st situation, a grievant has a case with 1 issue and 1
decision. so the data in the tables would look like this:

case
caseid = 100
social security number = xxx-xx-xxxx
lawyerid = 1234
contact date = 10/1/04
decision date = 10/6/04

caseresults
id = 33
caseid = 100
issueid = 12
decisionid = 6

in a 2nd situation, a grievant has a case with 2 issues and 2
decisions.

case
caseid = 101
social security number = yyy-yy-yyyy
lawyerid = 6789
contact date = 10/10/04
decision date = 10/20/04

caseresults
id = 33
caseid = 100
issueid = 12
decisionid = 6

caseresults
id = 34
caseid = 100
issueid = 15
decisionid = 9

there is a many to many relationship between

each issue has its own decision associated with it.
since there is a many to many relationship between case and issue

.................................................. ..............................
V. examples of the tables' structure
.................................................. ..............................
table: grievant
social security number (primary key)
last name
first name
address
region (north, south, east, west)

table: lawyer
lawyerid (primary key)
firm
last name
first name
work phone

table: case
caseid (primary key)
social security number (foreign key)
lawyerid (foreign key)
contact date
decision date

table: caseresults
id (primary key - autonumber)
caseid (foreign key)
issueid (foreign key)
decisionid (foreign key)

table: issue
issueid (primary key)
issuedescription

table: decision
decisionid (primary key)
decisiondescription

.................................................. ..............................
VI. Examples of values in Issue Table and Decision Table
.................................................. ..............................

table: issue
issueid (primary key) | issuedescription

1 | alcohol abuse
2 | drug use
3 | sexual harassment
4 | theft
table: decision
decisionid (primary key) | decisiondescription

1 | guilty
2 | not guilty
3 | pleaded out

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'll only respond to a few of your topics:

IV. As I interpret your description, there is really a 1 to many
relationship between cases and issues. IOW, one case can have many
issues. Since there is only 1 decision per issue, the relationship
between issues and decisions is 1 to 1. Therefore, the design of the
cases, issues & decisions is like this:

Cases - 1:m -> CaseResults

The decision ID is a column in the CaseResults.

The DDL for the Cases:CaseResults relationship is:

CREATE TABLE Cases (
CaseID Counter NOT NULL , -- a Counter data type is an AutoNumber
SSN Text(11) NOT NULL REFERENCES Grievant ,
LawyerID Long NOT NULL REFERENCES Lawyer ,
ContactDate Date ,
DecisionDate Date ,
CONSTRAINT PK_Case PRIMARY KEY (SSN, LawyerID)
)

== REFERENCES is the foreign key declaration

CREATE TABLE CaseResults (
CaseID Long REFERENCES Cases ,
IssueID Long REFERENCES Issues ,
DecisionID Long REFERENCES Decisions ,
CONSTRAINT PK_CaseResults PRIMARY KEY (CaseID, IssueID)
)

Since the PK of CaseResults is CaseID and IssueID, there can be many
issues to one case, and only one decision for each issue.

V. It is usually better to have a GrievantID (AutoNumber) as the
related column between the Grievants table and its dependent tables
rather than the SSN column. This is usually a security concern
(decrease the number of times the SSN is propagated in the db). Also,
if the SSN is changed (rare I'll admit), that change won't have to be
cascaded down to the dependent tables. The SSN should be the Primary
Key of the Grievants table: you can set a Unique index on the
GrievantID to keep it as the related column for dependent tables.

VI. It is usually meaningless to set the AutoNumber as the PK. E.g.,
when the PK is on the DecisionID, the following could occur:

Decisions table:
DecisionID DecisionDescription
1 Guilty
2 Not Guilty
3 Guilty
4 Pleaded out

Since the PK is the DecisionID, IDs 1 & 3 do not produce a Duplicate
Record error 'cuz they are different values. The true value that
defines each record as a separate record is the description column.
Therefore, the PK should be the DecisionDescription column. Then when
the user entered a description of "Guilty" more than once the Duplicate
Record error would arise.

Some developers would say that you can set up a Unique index on the
Description column and keep the PK on the ID column. Even though that
would work I don't do that 'cuz it seems to violate the original meaning
of the PK. I set up the description column as the PK column and then
set a unique index on the ID column so I can still use the ID as the
relationship column between tables. Boils down to "Six of one, half
dozen of the other."

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQX4NLYechKqOuFEgEQK58ACg4I4sStjAaAh6+djBtjiGxl FYigoAoMAr
+Zr0OPmq3J5owcMtjr2vK1/6
=RIJc
-----END PGP SIGNATURE-----
Megan wrote:

quick summary: i'm having problems trying to group fields in a report
in order to calculate percentages. to calculate percentages, i'm
comparing the results from my grouped fields to the totals.

first, let me say that this is a really long post. i wasn't sure how
much information/ background to provide, so i thought more was better
than less. i tried to delineate certain areas so that it would be
easy to peruse my posting and find the info you're looking for. the
first two sections describe the situation and the reports i'm trying
to create. the remaining sections deal with the relationships and
structure of the db, which isn't necessary for you to read.

here is an outline of my post:

I. Background data for the Reports i'm trying to create
II. Ideas for creating the reports
III. Background
IV. Examples
V. Table Structure
VI. Examples of values in Issue Table and Decision Table

.................................................. ....................................
I. Background data for the Reports i'm trying to create
.................................................. ....................................

let's say i have 3 lawyers: bieber, foster, and marks. to keep the
division simple and easy, let's say we have 15 cases. since a case
can have 1 or more issues and 1 or more decisions, let's say we have
9 cases that each has 2 issues and 2 decisions. the remaining 6 cases
have 1 issue and 1 decision each. so, we have:

9 of the 15 cases have 2 issues and 2 decisions
6 of the 15 cases have 1 issue and 1 decision

to get the total number of issues:
9 cases x 2 issues = 18 issues
6 cases x 1 issue = 6 issues
total number of issues = 18 + 6 = 24 issues

NOTE: the same goes for decisions.

to get the total number of decisions:
9 cases x 2 decisions = 18 decisions
6 cases x 1 decision = 6 issues
total number of decisions = 18 + 6 = 24 decisions

so, it all boils down to this:

3 lawyers
15 total cases
24 issues
24 decisions
.................................................. .............................
II. Ideas for creating the reports
.................................................. .............................

i want to compare the decisions the lawyers get.

of the 24 total decisions:
8 = not guilty
5 = guilty
11 = pleaded out

bieber = 2 not guilty, 2 guilty, and 4 pleaded out.
foster = 6 not guilty, 1 guilty, and 3 pleaded out.
marks = 0 not guilty, 2 guilty, and 4 pleaded out.

i want to create a report that groups the lawyers and groups the
decisions that lawyer has received.

then i want to compare the decisions each lawyer gets to the total
number of decisions in order to get percentages.

for example, i want to calculate 2 types of percentages for each
lawyer.

1.) bieber = 2 not guilty, 2 guilty, and 4 pleaded out.

therefore:

bieber = 2/8 not guilty, 2/5 guilty, and 5/11 pleaded out.

2.) bieber = 2 not guilty, 2 guilty, and 4 pleaded out.

therefore: 2 + 2 + 4 = 8 total decisions for bieber. so, bieber got
8/24 (24 being the total number of decisions)
how i did it: i grouped by decision, then by lawyer name, but the
report turns out ugly. i ran a crosstab query and that turned out
nicely, but i don't think i can use a crosstab query for a report.

does anyone have any suggestions on how to format/ group a report like
this? thanks in advance!

.................................................. .............................
III. Background
.................................................. .............................

i have a court rulings database that records information about
grievants and their cases. the db records personal information about
grievants, such as social security number, first name, last name,
address, etc...

i have a 1 to many relationship between grievant and case:

each grievant can have 1 or more cases.
each case can have 1 and only 1 grievant.

the table, case, records information about a grievant's case, such as
caseid, the grievant's social security number, case contact date,
decision date, etc...

each case can have 1 or more issues, and each case can have 1 or more
decisions. the table, case, has 2 many to many relationships.

case has a many to many relationship with issue, and case has a many
to many relationship with decision.

therefore, i created a single bridge table between case and issue and
between case and decision. this bridge table, caseresults, has 4
fields:

id: the primary key is an autonumber
caseid: for the case
issueid: for the issue
decisionid: for the decision
issue
/
case - caseresults
\
decision


.................................................. .............................
IV. here are 2 examples of what might happen:
.................................................. .............................

in the 1st situation, a grievant has a case with 1 issue and 1
decision. so the data in the tables would look like this:

case
caseid = 100
social security number = xxx-xx-xxxx
lawyerid = 1234
contact date = 10/1/04
decision date = 10/6/04

caseresults
id = 33
caseid = 100
issueid = 12
decisionid = 6

in a 2nd situation, a grievant has a case with 2 issues and 2
decisions.

case
caseid = 101
social security number = yyy-yy-yyyy
lawyerid = 6789
contact date = 10/10/04
decision date = 10/20/04

caseresults
id = 33
caseid = 100
issueid = 12
decisionid = 6

caseresults
id = 34
caseid = 100
issueid = 15
decisionid = 9

there is a many to many relationship between

each issue has its own decision associated with it.
since there is a many to many relationship between case and issue

.................................................. .............................
V. examples of the tables' structure
.................................................. .............................
table: grievant
social security number (primary key)
last name
first name
address
region (north, south, east, west)

table: lawyer
lawyerid (primary key)
firm
last name
first name
work phone

table: case
caseid (primary key)
social security number (foreign key)
lawyerid (foreign key)
contact date
decision date

table: caseresults
id (primary key - autonumber)
caseid (foreign key)
issueid (foreign key)
decisionid (foreign key)

table: issue
issueid (primary key)
issuedescription

table: decision
decisionid (primary key)
decisiondescription

.................................................. .............................
VI. Examples of values in Issue Table and Decision Table
.................................................. .............................

table: issue
issueid (primary key) | issuedescription

1 | alcohol abuse
2 | drug use
3 | sexual harassment
4 | theft
table: decision
decisionid (primary key) | decisiondescription

1 | guilty
2 | not guilty
3 | pleaded out