Percent calculation based on cross-tab query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jon - 18 Aug 2006 18:26 GMT

I have a cross-tab which works exactly as I want it to - a simple sum
of all value for a given month for each supplier:

TRANSFORM (Sum([xSqFtTable].[SqFt])) AS SumOfSqFt
SELECT xSqFtTable.Name, Sum(xSqFtTable.SqFt) AS [Total Of SqFt]
FROM xSqFtTable
GROUP BY xSqFtTable.Name
PIVOT Format([Date],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Which results in something that looks akin to this:

Name Jan Feb
Alpha 1 2
Beta 2 3
Gamma 5 1

Now I need to calculate the percentage for each supplier for each month
or in other words this:

Name Jan Feb
Alpha .125 .333
Beta .25 .5
Gamma .675 .167

I reckon I can get what I need by changing the expression after the
TRANSFORM in the first line, but I've had no luck in actually pulling
it off. Can anyone help me out by showing me how to do it?

Reply to this Message

Allen Browne - 18 Aug 2006 18:39 GMT

Add the SqFt field to the query grid again, choosing Sum in the Total row,
and Column Heading in the Crosstab row.

Now you can create a report a text box with properties:
Control Source =[Jan] / [SumOfSqFt]
Format Percent

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I have a cross-tab which works exactly as I want it to - a simple sum
> of all value for a given month for each supplier:
[quoted text clipped - 24 lines]
> TRANSFORM in the first line, but I've had no luck in actually pulling
> it off. Can anyone help me out by showing me how to do it?

Reply to this Message

Jon - 18 Aug 2006 19:22 GMT

Tried it. Got an error message: "You must enter Group By in the Total
Row for a field that has Column Heading in the Crosstab row".

> Add the SqFt field to the query grid again, choosing Sum in the Total row,
> and Column Heading in the Crosstab row.
[quoted text clipped - 36 lines]
> > TRANSFORM in the first line, but I've had no luck in actually pulling
> > it off. Can anyone help me out by showing me how to do it?

Reply to this Message

Allen Browne - 19 Aug 2006 06:47 GMT

Sorry, Jon: it's a Row Heading.

A crosstab can have only one Column Heading field, of course.

The total appears to the left of the month columns in the query itself, but
you can place it wherever you want on your report.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Tried it. Got an error message: "You must enter Group By in the Total
> Row for a field that has Column Heading in the Crosstab row".
[quoted text clipped - 35 lines]
>> > TRANSFORM in the first line, but I've had no luck in actually pulling
>> > it off. Can anyone help me out by showing me how to do it?

Reply to this Message

Jon - 21 Aug 2006 14:56 GMT

That gives me the sum of the row - which I already had. I need to
somehow get the sum of the column so I can calculate the percentage.

> Sorry, Jon: it's a Row Heading.
>
[quoted text clipped - 47 lines]
> >> > TRANSFORM in the first line, but I've had no luck in actually pulling
> >> > it off. Can anyone help me out by showing me how to do it?

Reply to this Message

Allen Browne - 21 Aug 2006 16:46 GMT

I think this will have to be another query into the first one.

You can then create the field:
[Jan] / [Total]
and set the Format of the field to Percent.
Same for other fields.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> That gives me the sum of the row - which I already had. I need to
> somehow get the sum of the column so I can calculate the percentage.
[quoted text clipped - 48 lines]
>> >> > pulling
>> >> > it off. Can anyone help me out by showing me how to do it?

Reply to this Message

Jon - 22 Aug 2006 22:06 GMT

Thanks Allen, I've solved the problem thanks to your advice and just as
a record for anyone else who reads this thread wondering if it'll solve
their problem, here's how I solved mine:

The original crossquote (which I posted earlier) is called qryReport17.
I created a new query (called qryReport17_MonthlyTotal) which drew upon
the original crossquote to give me a total for each month:

SELECT Sum(qryReport17.[Total Of SqFt]) AS [SumOfTotal Of SqFt],
Sum(qryReport17.Jan) AS SumOfJan, Sum(qryReport17.Feb) AS SumOfFeb,
Sum(qryReport17.Mar) AS SumOfMar, Sum(qryReport17.Apr) AS SumOfApr,
Sum(qryReport17.May) AS SumOfMay, Sum(qryReport17.Jun) AS SumOfJun,
Sum(qryReport17.Jul) AS SumOfJul, Sum(qryReport17.Aug) AS SumOfAug,
Sum(qryReport17.Sep) AS SumOfSep, Sum(qryReport17.Oct) AS SumOfOct,
Sum(qryReport17.Nov) AS SumOfNov, Sum(qryReport17.Dec) AS SumOfDec
FROM qryReport17;

Then I wrote a third query which does the actual division:

SELECT qryReport17.Name,
qryReport17!Jan/qryReport17_MonthlyTotal!SumOfJan AS JanPerc,
qryReport17!Feb/qryReport17_MonthlyTotal!SumOfFeb AS FebPerc,
qryReport17!Mar/qryReport17_MonthlyTotal!SumOfMar AS MarPerc,
qryReport17!Apr/qryReport17_MonthlyTotal!SumOfApr AS AprPerc,
qryReport17!May/qryReport17_MonthlyTotal!SumOfMay AS MayPerc,
qryReport17!Jun/qryReport17_MonthlyTotal!SumOfJun AS JunPerc,
qryReport17!Jul/qryReport17_MonthlyTotal!SumOfJul AS JulPerc,
qryReport17!Aug/qryReport17_MonthlyTotal!SumOfAug AS AugPerc,
qryReport17!Sep/qryReport17_MonthlyTotal!SumOfSep AS SepPerc,
qryReport17!Oct/qryReport17_MonthlyTotal!SumOfOct AS OctPerc,
qryReport17!Nov/qryReport17_MonthlyTotal!SumOfNov AS NovPerc,
qryReport17!Dec/qryReport17_MonthlyTotal!SumOfDec AS DecPerc,
qryReport17![Total Of SqFt]/qryReport17_MonthlyTotal![SumOfTotal Of
SqFt] AS TotalPerc
FROM qryReport17, qryReport17_MonthlyTotal;

And if anyone from the Microsoft Access design team is reading this,
that's at least one more query than should be needed to do the job of
calculating percentage if you ask me...

> I think this will have to be another query into the first one.
>
[quoted text clipped - 60 lines]
> >> >> > pulling
> >> >> > it off. Can anyone help me out by showing me how to do it?

Reply to this Message