Percentages is crosstab query.

DPCpresto · Sep 13, 2003

Aloha,

I have a crosstab query that lists Countries as the row headings, number of females as one column heading, number of males as another column heading, and finally, total of both Male+Female as last column. Is it possible to also extract percentages for the male and female columns? I would like to do this within ACCESS 2000 and not export to Excel for the math. This would also help me with several of my other, similar, crosstab queries. Following is the query example:

Country TotalofID Female Male
USA 8 4 4
GER 10 2 8
etc......

My SQL for the above is...

TRANSFORM Count(AllAthletes2003.ID) AS CountOfID
SELECT AllAthletes2003.Country, Count(AllAthletes2003.ID) AS [Total Of ID]
FROM AllAthletes2003
GROUP BY AllAthletes2003.Country
PIVOT AllAthletes2003.Gender;

What I want is this...

Country TotalofID Female Male Female% Male% OverallF% OverallM%
USA 8 4 4 50% 50% 66% 33%
GER 10 2 8 20% 80% 33% 66%

Thanks in advance for the help!
Mahalo,
David P. Cobb

Duane Hookom · Sep 14, 2003

Is there a reason why you don't want to do this in a report where you can create text boxes with control sources of:
=[Female]/([Female]+[Male])
=[Male]/([Female]+[Male])
=[Female]/Sum([Female])
=[Male]/Sum([Male])

--
Duane Hookom
MS Access MVP

Aloha,

I have a crosstab query that lists Countries as the row headings, number of females as one column heading, number of males as another column heading, and finally, total of both Male+Female as last column. Is it possible to also extract percentages for the male and female columns? I would like to do this within ACCESS 2000 and not export to Excel for the math. This would also help me with several of my other, similar, crosstab queries. Following is the query example:

Country TotalofID Female Male
USA 8 4 4
GER 10 2 8
etc......

My SQL for the above is...

TRANSFORM Count(AllAthletes2003.ID) AS CountOfID
SELECT AllAthletes2003.Country, Count(AllAthletes2003.ID) AS [Total Of ID]
FROM AllAthletes2003
GROUP BY AllAthletes2003.Country
PIVOT AllAthletes2003.Gender;

What I want is this...

Country TotalofID Female Male Female% Male% OverallF% OverallM%
USA 8 4 4 50% 50% 66% 33%
GER 10 2 8 20% 80% 33% 66%

Thanks in advance for the help!
Mahalo,
David P. Cobb

DPCpresto · Sep 14, 2003

Aloha Duane and thanks for taking the time to reply. To answer your question, the main reason I don't want this in a report is just a matter of convenience and time...if I can get the result in the query, then it's less time, and fewer steps, and a tighter printout without all the extra fluff of the report format.

Dave
Is there a reason why you don't want to do this in a report where you can create text boxes with control sources of:
=[Female]/([Female]+[Male])
=[Male]/([Female]+[Male])
=[Female]/Sum([Female])
=[Male]/Sum([Male])

--
Duane Hookom
MS Access MVP

Aloha,

I have a crosstab query that lists Countries as the row headings, number of females as one column heading, number of males as another column heading, and finally, total of both Male+Female as last column. Is it possible to also extract percentages for the male and female columns? I would like to do this within ACCESS 2000 and not export to Excel for the math. This would also help me with several of my other, similar, crosstab queries. Following is the query example:

Country TotalofID Female Male
USA 8 4 4
GER 10 2 8
etc......

My SQL for the above is...

TRANSFORM Count(AllAthletes2003.ID) AS CountOfID
SELECT AllAthletes2003.Country, Count(AllAthletes2003.ID) AS [Total Of ID]
FROM AllAthletes2003
GROUP BY AllAthletes2003.Country
PIVOT AllAthletes2003.Gender;

What I want is this...

Country TotalofID Female Male Female% Male% OverallF% OverallM%
USA 8 4 4 50% 50% 66% 33%
GER 10 2 8 20% 80% 33% 66%

Thanks in advance for the help!
Mahalo,
David P. Cobb

Duane Hookom · Sep 14, 2003

I would probably first change the query to add the Column Headings:
TRANSFORM Count(AllAthletes2003.ID) AS CountOfID
SELECT AllAthletes2003.Country, Count(AllAthletes2003.ID) AS [Total Of ID]
FROM AllAthletes2003
GROUP BY AllAthletes2003.Country
PIVOT AllAthletes2003.Gender IN ("Female","Male");

Then, create a second query based on your first that calculates the Female%
and Male%. To get the OverallX%, you can use a subquery.
OverAllF: Female/(Select Sum(Female) From qxtbYourCrosstab)
OverAllM: Female/(Select Sum(Male) From qxtbYourCrosstab)
--
Duane Hookom
MS Access MVP

Aloha Duane and thanks for taking the time to reply. To answer your
question, the main reason I don't want this in a report is just a matter of
convenience and time...if I can get the result in the query, then it's less
time, and fewer steps, and a tighter printout without all the extra fluff of
the report format.

Dave
Is there a reason why you don't want to do this in a report where you can
create text boxes with control sources of:
=[Female]/([Female]+[Male])
=[Male]/([Female]+[Male])
=[Female]/Sum([Female])
=[Male]/Sum([Male])

--
Duane Hookom
MS Access MVP

Aloha,

I have a crosstab query that lists Countries as the row headings,
number of females as one column heading, number of males as another column
heading, and finally, total of both Male+Female as last column. Is it
possible to also extract percentages for the male and female columns? I
would like to do this within ACCESS 2000 and not export to Excel for the
math. This would also help me with several of my other, similar, crosstab
queries. Following is the query example:

Country TotalofID Female Male
USA 8 4 4
GER 10 2 8
etc......

My SQL for the above is...

TRANSFORM Count(AllAthletes2003.ID) AS CountOfID
SELECT AllAthletes2003.Country, Count(AllAthletes2003.ID) AS [Total Of ID]
FROM AllAthletes2003
GROUP BY AllAthletes2003.Country
PIVOT AllAthletes2003.Gender;

What I want is this...

Country TotalofID Female Male Female% Male% OverallF%
OverallM%
USA 8 4 4 50% 50%
66% 33%
GER 10 2 8 20% 80%
33% 66%

Thanks in advance for the help!
Mahalo,
David P. Cobb

Groucho · Sep 14, 2003

"Duane Hookom" wrote in message

I would probably first change the query to add the Column Headings:
TRANSFORM Count(AllAthletes2003.ID) AS CountOfID
SELECT AllAthletes2003.Country, Count(AllAthletes2003.ID) AS [Total Of ID]
FROM AllAthletes2003
GROUP BY AllAthletes2003.Country
PIVOT AllAthletes2003.Gender IN ("Female","Male");

Then, create a second query based on your first that calculates the Female%
and Male%. To get the OverallX%, you can use a subquery.
OverAllF: Female/(Select Sum(Female) From qxtbYourCrosstab)
OverAllM: Female/(Select Sum(Male) From qxtbYourCrosstab)

I'd by happy to explain to MS how to change their xtab to
do this stuff easily.But something tells me I shouldn't bother
waiting by the phone

.

For all types of xtabs and more without complicated sql coding
on Sql Server 2000 check out RAC @
www.rac4sql.net

Duane Hookom · Sep 14, 2003

I didn't see SQL Server mentioned anywhere in this thread. I doubt the OP
would upgrade to SQL Server just to purchase your product to get this one
feature no matter how good and powerful RAC is.

--
Duane Hookom
MS Access MVP

Groucho said:
"Duane Hookom" wrote in message

I would probably first change the query to add the Column Headings:
TRANSFORM Count(AllAthletes2003.ID) AS CountOfID
SELECT AllAthletes2003.Country, Count(AllAthletes2003.ID) AS [Total Of ID]
FROM AllAthletes2003
GROUP BY AllAthletes2003.Country
PIVOT AllAthletes2003.Gender IN ("Female","Male");

Then, create a second query based on your first that calculates the Female%
and Male%. To get the OverallX%, you can use a subquery.
OverAllF: Female/(Select Sum(Female) From qxtbYourCrosstab)
OverAllM: Female/(Select Sum(Male) From qxtbYourCrosstab)

Click to expand...

I'd by happy to explain to MS how to change their xtab to
do this stuff easily.But something tells me I shouldn't bother
waiting by the phone.

For all types of xtabs and more without complicated sql coding
on Sql Server 2000 check out RAC @
www.rac4sql.net

Groucho · Sep 15, 2003

I didn't see SQL Server mentioned anywhere in this thread. I doubt the OP
would upgrade to SQL Server just to purchase your product to get this one
feature no matter how good and powerful RAC is.

Ouch

Well sometimes you just gotta gotta say - 'What the .....'
(You recall 'Risky Business'

.
Besides there's plenty of cd's with msde hanging around

Duane Hookom · Sep 15, 2003

Don't get me wrong on this. I don't mind a plug for a good product once in a
while... I have suggested third party products in the past and I think even
RAC once...

Percentages is crosstab query.

DPCpresto

Duane Hookom

DPCpresto

Duane Hookom

Groucho

Duane Hookom

Groucho

Duane Hookom