Expression Percent Crosstab 1

[matrixindicator]

I have a crosstab query with in it for the value a Count. This works, but I need a second Crosstab who gives a percent value related to the total of the Column.

25
25
30
20
----
100

I think I need an expression and second how can you say count the total of the column in your expression.
expression something like :

 Count * 100/Total of the Column

 

[oharab]

Can you post your SQL so we can see how your data is structured.

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.

 

[matrixindicator]

TRANSFORM Count([EMPLEADOSGRUPO1])*100/ ... AS EMPLEADOSGRUPO2
SELECT [Qry].SectorId
FROM Qry
GROUP BY [Qry].SectorId
PIVOT [Qry].EMPLEADOSGRUPO;

I use an expression, in this expression I should add
/ (divide) by the total of the column to get the percent. In a crosstab you can have a total for the row, but how to get a total for the column in the expression ?

 

[oharab]

matrixindicator,
I don't think you can do what you want in a single crosstab query.
Your best bet is to do the Tabulation in a seperate query and calculate the rest afterwards, so

TRANSFORM Count(*)
SELECT Qry.SectorId,[COLOR=red yellow][b]Count(*) AS RowTotal[/b][/color]

FROM Qry
GROUP BY Qry.SectorId
PIVOT Qry.EMPLEADOSGRUPO;

as qry2.
The highlighted bit will then put an extra column in your query with the total of all the rows.

You can then use another query to look at qry2 to calculate the percentages.

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.

 

[fneily]

Anytime you need %ages, think Pivot Table.
Create a query to group and total. You'll use this as the source for a Pivottable.
GoTo Forms, click New and select Pivot Table Wizard and at the bottom select the query for the source. Click Ok. In the wizard, click next to go past the intro. In the next window, select both fields. Click next. You'll be in Excel with the PivotTable Wizard showing. Click Layout. Drag the rectangle on the right that you want as Row headings to the area Row. Drag the numeric to the Data area. Double click the button that says SUM of ... You'll see the Pivot table Field window. Click the Options button. Where it says "Show Data As" click the down arrow and select "% of Column". Click the Number button and select 0 for Decimal Places. Click Ok. Click Ok. Click Ok. On the Pivottable Wizard window, click Options. DESelect Grand Totals for Rows. Click Ok. Click Finish. Switch to Form view. BINGO! Close Excel.

 

[oharab]

Thanks for the tip, fneily.
I've not used the pivot table form before, so didn't really consider it.
It's not what the OP asked, but is certainly another way of answering the question.

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.