Percentages is crosstab query.

D

DPCpresto

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
 
D

Duane Hookom

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
 
D

DPCpresto

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
 
D

Duane Hookom

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
 
G

Groucho

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

Duane Hookom

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
Click to expand...
 
G

Groucho

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.
Click to expand...

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:)
 
D

Duane Hookom

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Percentages, eg. Male v Female 1
How do I check if there is a field in a query? 2
Complex Crosstab Query 2
Null Month records for a field 2
dynamic crosstab query 2
Select Query & Crosstab Query 1
Problems with Crosstab Query 4
Crosstab Query Column headings 4

Top