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...
Allen said:
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.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Jon said:
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.
Allen said:
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.
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".
Allen Browne wrote:
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
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?
