Calculation Question

[Guest]

I have a report (RPT_ADMISSIONS) that has a recordsource of QRY_ADMISSIONS.

The Qry_Admissions contains several demographic data such as Black, White,
Male, Female, etc...this data is imported from an older system we use at
work. I need to make a report that will group and count the number of
demographics based on certain criteria. My problem is the count.

In each field such as Black, if the person of that record is black, the
field will contain a value of a number >0. If the person is not black, the
value will be 0. I need to count all of the people for each column who have
a greater than 0 value and display this on the report.

Any idea how this can be accomplished at the report level - or is this
something I will have to do basing a query off of the original query and then
pulling the values into the report based on the query of the query?

Any suggestions will be greatly appreciated.

Clay

 

[Pat Hartman\(MVP\)]

The original data is not normalized. It is best to normalize it as you
import it to facilitate querying the data. Having a field for black and a
separate field for white and presumably separate fields for Hispanic, etc.
makes calculating statistics harder than it needs to be. Each choice should
be mutually exclusive or if you really want to allow multiple choices, you
need to use a separate table in which to store them.

To work with the data as you have it requires an IIf() within a Sum() for
each field:

Select Sum(IIf(Black > 0, 1, 0)) As BlackSum, Sum(IIf(White >0 ,1,0)) As
WhiteSum, Sum(IIf(Hispanic > 0, 1,0)) As HispanicSum, Sum(IIf(Sex= "Male",
1, 0)) As SumMale, Sum(IIf(Sex= "Female", 1, 0)) As SumFemale etc.


You'll need separate queries if you want to produce counts of White Females
or Hispanic Males for example:
Select "White Female" As Category, Count(*)
From YourTable
Where White > 0 AND Sex = "Female"
Group by "White Female", Category;

Select "Black Female" As Category, Count(*)
From YourTable
Where Black > 0 AND Sex = "Female"
Group by "Black Female", Category;

Select "Hispanic Female" As Category, Count(*)
From YourTable
Where Hispanic > 0 AND Sex = "Female"
Group by "Hispanic Female", Category;
etc. for additional race groupings
And double the whole set for males. Plus these queries will not account for
columns with null values.

However, you need only a TOTAL of THREE totals queries for all combinations
regardless of how many race/sex combinations you have if you properly
normalize the data:
Select Race, Count(*) as RaceCount
From YourTable
Group By Race;

Select Sex, Count(*) As SexCount
From YourTable
Group by Sex;

Select Race, Sex, Count(*) As RaceSexCount
From YourTable
Group by Race, Sex;

 

[Guest]

You should not be using a separate field for each but a separate for each
category like --
Race - use either text label or number with a table in a one-to-many
relationship
Sex - "M" or "F"
Age - number

 

[John Vinson]

I have a report (RPT_ADMISSIONS) that has a recordsource of QRY_ADMISSIONS.

The Qry_Admissions contains several demographic data such as Black, White,
Male, Female, etc...this data is imported from an older system we use at
work. I need to make a report that will group and count the number of
demographics based on certain criteria. My problem is the count.

In each field such as Black, if the person of that record is black, the
field will contain a value of a number >0. If the person is not black, the
value will be 0. I need to count all of the people for each column who have
a greater than 0 value and display this on the report.

Any idea how this can be accomplished at the report level - or is this
something I will have to do basing a query off of the original query and then
pulling the values into the report based on the query of the query?

I'd base the report on a Query containing some calculated fields: e.g.

IsBlack: IIF([Black] > 0, 1, 0)

You could then Sum these calculated fields (on the report Footer, or a
section footer, or even in a Totals query) to get the count of people
in each group.

John W. Vinson[MVP]