Need percentage from 2 rows in a crosstab query

[Shinken]

With a crosstab query that looks like...

qry1
GENDER....SiteA.....SiteB....SiteC...etc
Female......2.........3........1...
Male........2.........7........9...

I need a display that shows the percent Male or Female, such as:

qry2
GENDER....SiteA.....SiteB....SiteC...etc
Female......2.........3........1...
Male........2.........7........9...
%Female....50........30.......10...

Alternately, query that produces a single percent row would also work, such as:

qry3
GENDER....SiteA.....SiteB....SiteC...etc
%Female....50........30.......10...

as I could then do a UNION of qry1 & qry3 to produce qry2.

Getting percent from columns isn't particularly difficult, but getting percent from rows seems to be a challenge.

Any suggestions are appreciated.

Thanks,

Shin

 

[zerosandones]

No problem ... this will work just fine:

Using table tblGender (Site, Gender, GenderCount):

TRANSFORM (Sum(Switch([Gender]="F",[GenderCount],True,0))/Sum([GenderCount]))*100 AS FemalePercent
SELECT "Female %" AS GenderF
FROM tblGender
GROUP BY "Female %"
PIVOT tblGender.Site;

NOTE: if you prefer the previous format you described, you'll need a dummy table (I use a table called tblDual with one record and one field named "Dummy" having a value of "X&quot ... ORACLE users from days gone by will recognize use of tblDual. If you really need the first format you described, please reply and I'll give you that too.

Hope this helps,

00001111s

 

[Shinken]

zerosandones,

Thanks. Works well.

Shin