Multiple percentages

[janmd]

I have demographic data and want to use Access to work out % of males,
% of females, % in various age groups, % in various ethnic groups,
etc. Is there an easy way to do this without having to create a query
for each group, please?

 

[Access Developer]

You can create a Report, grouping on ethnic group, with the calculations in
the Group Header or Footer, so the calculations only have to be created
once, but will be executed for each Group. If all you want is a summary of
the percentages by group, you can simply not show the Detail lines in the
Report.

If, for some reason, your basic demographic data is not in a single table,
then you have problems in your table design, and should be asking about
that, not about calculations.

 

[James A. Fortune]

I have demographic data and want to use Access to work out % of males,
% of females, % in various age groups, % in various ethnic groups,
etc. Is there an easy way to do this without having to create a query
for each group, please?

Your easiest route is likely to be to use Aggregate Domain Functions.
Here is an example of using them:

tblCustomers
CustomerID AutoNumber
CustomerFirstName Text
CustomerLastName Text
Ethnicity Text
Gender Text

CustomerID CustomerFirstName CustomerLastName Ethnicity Gender
1 Jan Schmidt German Null (i.e., blank)
2 Daniel van Duyne Dutch Male
3 Sally Ouillette Null Female
4 Adolph Mavis German Male
5 Greta Richter German Female
6 Dreia Geist German Female

qryCustomerPercentages:

SELECT DCount("*","tblCustomers","Gender IS NOT NULL") AS
GenderedCustomers,
DCount("*","tblCustomers","Ethnicity IS NOT NULL") AS
EthnicizedCustomers,
Int(10*CDbl(IIf(GenderedCustomers >
0,DCount("Gender","tblCustomers","Gender='Male'")*100/
GenderedCustomers,50))+0.5)/10 AS PercentMaleCustomers,
100-PercentMaleCustomers AS PercentFemaleCustomers,
Int(10*CDbl(IIf(EthnicizedCustomers >
0,DCount("Ethnicity","tblCustomers","Ethnicity='German'")*100/
EthnicizedCustomers,0))+0.5)/10 AS PercentGermanCustomers
FROM tblCustomers
WHERE CustomerID = 1;

!qryCustomerPercentages:
GenderedCustomers EthnicizedCustomers PercentMaleCustomers
PercentFemaleCustomers PercentGermanCustomers
5 5 40 60 80

Note: The expression Int(10 * x + 0.5) / 10.0 is used to round x to
the nearest tenth, with numbers ending in .05, .15, etc. chopped to .
0, .1, etc. so that percentages like 33.3333333333333 do not show up.
No customers is assumed to result in 50 percent male and 50 percent
female . No customers is also assumed to result in 0 percent
German customers.

The CustomerID = 1 should use the first CustomerID in the table rather
than 1. That was used so because only one record from tblCustomers
needed to be selected because only aggregates were shown. The SQL
shown is only an example that handles the most common error causing
situations. If the query results are going to a report, then report
grouping can be used instead to aggregate the data.

James A. Fortune
(e-mail address removed)

Disclaimer: Any programming examples shown are for illustration
purposes only, without warranty either expressed or implied. This
includes, but is not limited to, the implied warranties of
merchantability or fitness for a particular purpose. This post assumes
that you are familiar with the programming language that is being
demonstrated and with the tools that are used to create and to debug
procedures. I might explain the functionality of a particular
procedure, but I am under no obligation to modify these examples to
provide added functionality or to construct procedures to meet your
specific requirements. Any code samples posted contain no known
hidden material defects. However, anyone who uses any code sample
posted does so with the understanding that they are responsible for
any testing of any illustrative code sample for any particular use.
Furthermore, anyone using an illustrative code sample I provide or
code derived from it does so at their own risk.

 

[James A. Fortune]

I have demographic data and want to use Access to work out % of males,
% of females, % in various age groups, % in various ethnic groups,
etc. Is there an easy way to do this without having to create a query
for each group, please?

Repost:

The easiest way is to use Domain Aggregate Functions. Here is an
example:

tblCustomers
CustomerID AutoNumber
CustomerFirstName Text
CustomerLastName Text
Ethnicity Text
Gender Text

CustomerID CustomerFirstName CustomerLastName Ethnicity Gender
1 Jan Schmidt German Null (i.e., blank)
2 Daniel van Duyne Dutch Male
3 Sally Ouillette Null Female
4 Adolph Mavis German Male
5 Greta Richter German Female
6 Dreia Geist German Female

qryCustomerPercentages:
SELECT DCount("*","tblCustomers","Gender IS NOT NULL") AS
GenderedCustomers,
DCount("*","tblCustomers","Ethnicity IS NOT NULL") AS
EthnicizedCustomers,

Int(10*CDbl(IIf(GenderedCustomers>0,DCount("Gender","tblCustomers","Gender='Male'")*100/
GenderedCustomers,50))+0.5)/10 AS PercentMaleCustomers,
100-PercentMaleCustomers AS PercentFemaleCustomers,

Int(10*CDbl(IIf(EthnicizedCustomers>0,DCount("Ethnicity","tblCustomers","Ethnicity='German'")*100/
EthnicizedCustomers,0))+0.5)/10 AS PercentGermanCustomers
FROM tblCustomers
WHERE CustomerID=1;

!qryCustomerPercentages:
GenderedCustomers EthnicizedCustomers PercentMaleCustomers
PercentFemaleCustomers PercentGermanCustomers
5 5 40 60 80

Notes:

1) Int(10 * X + 0.5) / 10 is used to round to the nearest decimal
place, chopping hundredths ending in 5.
2) In 'CustomerID = 1', the '1' should actually be the first
CustomerID in the table. This was done for efficiency since only
aggregated data is shown.
3) No customers is assumed to be 50% male and 50% female . No
customers is assumed to be 0% German.
4) The query is designed to overcome most, but not all errors
possible.

James A. Fortune
(e-mail address removed)

Disclaimer: Any programming examples shown are for illustration
purposes only, without warranty either expressed or implied. This
includes, but is not limited to, the implied warranties of
merchantability or fitness for a particular purpose. This post assumes
that you are familiar with the programming language that is being
demonstrated and with the tools that are used to create and to debug
procedures. I might explain the functionality of a particular
procedure, but I am under no obligation to modify these examples to
provide added functionality or to construct procedures to meet your
specific requirements. Any code samples posted contain no known
hidden material defects. However, anyone who uses any code sample
posted does so with the understanding that they are responsible for
any testing of any illustrative code sample for any particular use.
Furthermore, anyone using an illustrative code sample I provide or
code derived from it does so at their own risk.