Calculate in a Crosstab?

S

Susan L

Is it possible to calculate totals in a crosstab? I have a crosstab set up
that is based on a union query that separates values that are in columns in
the original table (Valid, Invalid), but that I need to report in rows. (The
xtab reports data by month.)I was going to do the calculations in the report,
but have run into difficulty, because the results of the union query put the
values for the former columns into a single field.

The report for must be set up as follows:
Transactions Jan Feb Mar etc to Dec Total %ofTotal

Valid 170,000 150,000 190,000 nnnn
pp.p%

Invalid 100,000 125,000 160,000 nnnn
pp.p%

Totals 270,000 275,000 350,000 nnnn pp.p%

% Valid 62.9 54.5 54.2 etc to Dec

I can get the totals OK becase the report control Sums the field, but am
unable to calculate the percentage, because I don't know of a way to refer to
the "Valid" part of the transactions field. Not knowing how to do that, I was
thinking maybe I could add two rows to the xtab query to do both the Totals
and % Valid. Is there a way? Otherwise, i guess I'll have to export to Excel,
which I'd prefer not to do.

Here's the xtab SQL:
TRANSFORM Sum(qry_ValInvlTrans_Count2_Union.[Month Total]) AS [SumOfMonth
Total]
SELECT qry_ValInvlTrans_Count2_Union.Transactions,
qry_ValInvlTrans_Count2_Union.Sequence,
Sum(qry_ValInvlTrans_Count2_Union.[Month Total]) AS [Total Of Month Total]
FROM qry_ValInvlTrans_Count2_Union
GROUP BY qry_ValInvlTrans_Count2_Union.Transactions,
qry_ValInvlTrans_Count2_Union.Sequence
ORDER BY qry_ValInvlTrans_Count2_Union.Sequence
PIVOT qry_ValInvlTrans_Count2_Union.Mth In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

And here's the union:

SELECT Format([STDT], "MMM") As Mth, "Valid" as [Transactions], "1" as
Sequence, [VLD_TXN_CNT] as
[Month Total]
FROM tbl_Trans_Val_Inval
UNION ALL SELECT Format([STDT], "MMM") As Mth, "Invalid" as [Transactions],
"2" as Sequence,
[IVLD_TXN_CNT] as [Month Total]
FROM tbl_Trans_Val_Inval;

If someone could let me know whether what I'm asking is possible (or not
possible), I'd be appreciative.
 
K

Ken Sheridan

Susan:

You should be able to do it in computed controls in the report, e.g. for
January:

=Format((Sum([Jan]*IIf([Transactions] = "Valid",1,0)))/Sum([Jan]),"Percent")

Ken Sheridan
Stafford, England
 
J

John Spencer

TRANSFORM Sum(qry_ValInvlTrans_Count2_Union.[Month Total]) AS
[SumOfMonth Total]
SELECT [Transactions],
[Sequence],
Sum([Month Total]) AS [Total Of Month Total

, Sum(IIF(Transactions="Valid",[Month Total],Null) as TotalValid

FROM qry_ValInvlTrans_Count2_Union
GROUP BY qry_ValInvlTrans_Count2_Union.Transactions,
qry_ValInvlTrans_Count2_Union.Sequence
ORDER BY qry_ValInvlTrans_Count2_Union.Sequence
PIVOT qry_ValInvlTrans_Count2_Union.Mth In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Susan said:
Is it possible to calculate totals in a crosstab? I have a crosstab set up
that is based on a union query that separates values that are in columns in
the original table (Valid, Invalid), but that I need to report in rows. (The
xtab reports data by month.)I was going to do the calculations in the report,
but have run into difficulty, because the results of the union query put the
values for the former columns into a single field.

The report for must be set up as follows:
Transactions Jan Feb Mar etc to Dec Total %ofTotal

Valid 170,000 150,000 190,000 nnnn
pp.p%

Invalid 100,000 125,000 160,000 nnnn
pp.p%

Totals 270,000 275,000 350,000 nnnn pp.p%

% Valid 62.9 54.5 54.2 etc to Dec

I can get the totals OK becase the report control Sums the field, but am
unable to calculate the percentage, because I don't know of a way to refer to
the "Valid" part of the transactions field. Not knowing how to do that, I was
thinking maybe I could add two rows to the xtab query to do both the Totals
and % Valid. Is there a way? Otherwise, i guess I'll have to export to Excel,
which I'd prefer not to do.

Here's the xtab SQL:
TRANSFORM Sum(qry_ValInvlTrans_Count2_Union.[Month Total]) AS [SumOfMonth
Total]
SELECT qry_ValInvlTrans_Count2_Union.Transactions,
qry_ValInvlTrans_Count2_Union.Sequence,
Sum(qry_ValInvlTrans_Count2_Union.[Month Total]) AS [Total Of Month Total]
FROM qry_ValInvlTrans_Count2_Union
GROUP BY qry_ValInvlTrans_Count2_Union.Transactions,
qry_ValInvlTrans_Count2_Union.Sequence
ORDER BY qry_ValInvlTrans_Count2_Union.Sequence
PIVOT qry_ValInvlTrans_Count2_Union.Mth In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

And here's the union:

SELECT Format([STDT], "MMM") As Mth, "Valid" as [Transactions], "1" as
Sequence, [VLD_TXN_CNT] as
[Month Total]
FROM tbl_Trans_Val_Inval
UNION ALL SELECT Format([STDT], "MMM") As Mth, "Invalid" as [Transactions],
"2" as Sequence,
[IVLD_TXN_CNT] as [Month Total]
FROM tbl_Trans_Val_Inval;

If someone could let me know whether what I'm asking is possible (or not
possible), I'd be appreciative.
Click to expand...
 
J

John Spencer

Never mind. I misread your posting.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

John said:
TRANSFORM Sum(qry_ValInvlTrans_Count2_Union.[Month Total]) AS
[SumOfMonth Total]
SELECT [Transactions],
[Sequence],
Sum([Month Total]) AS [Total Of Month Total

, Sum(IIF(Transactions="Valid",[Month Total],Null) as TotalValid

FROM qry_ValInvlTrans_Count2_Union
GROUP BY qry_ValInvlTrans_Count2_Union.Transactions,
qry_ValInvlTrans_Count2_Union.Sequence
ORDER BY qry_ValInvlTrans_Count2_Union.Sequence
PIVOT qry_ValInvlTrans_Count2_Union.Mth In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Susan said:
Is it possible to calculate totals in a crosstab? I have a crosstab
set up that is based on a union query that separates values that are
in columns in the original table (Valid, Invalid), but that I need to
report in rows. (The xtab reports data by month.)I was going to do the
calculations in the report, but have run into difficulty, because the
results of the union query put the values for the former columns into
a single field.

The report for must be set up as follows:
Transactions Jan Feb Mar etc to Dec Total
%ofTotal

Valid 170,000 150,000 190,000
nnnn pp.p%

Invalid 100,000 125,000 160,000
nnnn pp.p%

Totals 270,000 275,000 350,000
nnnn pp.p%

% Valid 62.9 54.5 54.2 etc to Dec

I can get the totals OK becase the report control Sums the field, but
am unable to calculate the percentage, because I don't know of a way
to refer to the "Valid" part of the transactions field. Not knowing
how to do that, I was thinking maybe I could add two rows to the xtab
query to do both the Totals and % Valid. Is there a way? Otherwise, i
guess I'll have to export to Excel, which I'd prefer not to do.

Here's the xtab SQL:
TRANSFORM Sum(qry_ValInvlTrans_Count2_Union.[Month Total]) AS
[SumOfMonth Total]
SELECT qry_ValInvlTrans_Count2_Union.Transactions,
qry_ValInvlTrans_Count2_Union.Sequence,
Sum(qry_ValInvlTrans_Count2_Union.[Month Total]) AS [Total Of Month
Total]
FROM qry_ValInvlTrans_Count2_Union
GROUP BY qry_ValInvlTrans_Count2_Union.Transactions,
qry_ValInvlTrans_Count2_Union.Sequence
ORDER BY qry_ValInvlTrans_Count2_Union.Sequence
PIVOT qry_ValInvlTrans_Count2_Union.Mth In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


And here's the union:

SELECT Format([STDT], "MMM") As Mth, "Valid" as [Transactions], "1" as
Sequence, [VLD_TXN_CNT] as [Month Total] FROM tbl_Trans_Val_Inval
UNION ALL SELECT Format([STDT], "MMM") As Mth, "Invalid" as
[Transactions], "2" as Sequence,
[IVLD_TXN_CNT] as [Month Total] FROM tbl_Trans_Val_Inval;

If someone could let me know whether what I'm asking is possible (or
not possible), I'd be appreciative.
Click to expand...
Click to expand...
 
S

Susan L

Ken: This was extremely helpful and is working well.

Now I'm trying to obtain a % of total for both values in the Transactions
field, Valid and Invalid. I gave a go at putting two IIf statements together
with no luck. Received an error about putting quotes around text. I'm using
two totals controls, called txtYTDTotal and txtGrandTotal).

Here's my attempt. I know the syntax must be wrong. (I guessed that the 1,0
in the function meant "true" else nothing.)

=Format(([txtYTDTotal]*(IIf([Transactions]="Valid",1,0),IIf([Transactions]="Invalid",1,0)))/[txtGrandTotal],"Percent")

Could you show me where my syntax is off?
--
susan


Ken Sheridan said:
Susan:

You should be able to do it in computed controls in the report, e.g. for
January:

=Format((Sum([Jan]*IIf([Transactions] = "Valid",1,0)))/Sum([Jan]),"Percent")

Ken Sheridan
Stafford, England

Susan L said:
Is it possible to calculate totals in a crosstab? I have a crosstab set up
that is based on a union query that separates values that are in columns in
the original table (Valid, Invalid), but that I need to report in rows. (The
xtab reports data by month.)I was going to do the calculations in the report,
but have run into difficulty, because the results of the union query put the
values for the former columns into a single field.

The report for must be set up as follows:
Transactions Jan Feb Mar etc to Dec Total %ofTotal

Valid 170,000 150,000 190,000 nnnn
pp.p%

Invalid 100,000 125,000 160,000 nnnn
pp.p%

Totals 270,000 275,000 350,000 nnnn pp.p%

% Valid 62.9 54.5 54.2 etc to Dec

I can get the totals OK becase the report control Sums the field, but am
unable to calculate the percentage, because I don't know of a way to refer to
the "Valid" part of the transactions field. Not knowing how to do that, I was
thinking maybe I could add two rows to the xtab query to do both the Totals
and % Valid. Is there a way? Otherwise, i guess I'll have to export to Excel,
which I'd prefer not to do.

Here's the xtab SQL:
TRANSFORM Sum(qry_ValInvlTrans_Count2_Union.[Month Total]) AS [SumOfMonth
Total]
SELECT qry_ValInvlTrans_Count2_Union.Transactions,
qry_ValInvlTrans_Count2_Union.Sequence,
Sum(qry_ValInvlTrans_Count2_Union.[Month Total]) AS [Total Of Month Total]
FROM qry_ValInvlTrans_Count2_Union
GROUP BY qry_ValInvlTrans_Count2_Union.Transactions,
qry_ValInvlTrans_Count2_Union.Sequence
ORDER BY qry_ValInvlTrans_Count2_Union.Sequence
PIVOT qry_ValInvlTrans_Count2_Union.Mth In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

And here's the union:

SELECT Format([STDT], "MMM") As Mth, "Valid" as [Transactions], "1" as
Sequence, [VLD_TXN_CNT] as
[Month Total]
FROM tbl_Trans_Val_Inval
UNION ALL SELECT Format([STDT], "MMM") As Mth, "Invalid" as [Transactions],
"2" as Sequence,
[IVLD_TXN_CNT] as [Month Total]
FROM tbl_Trans_Val_Inval;

If someone could let me know whether what I'm asking is possible (or not
possible), I'd be appreciative.
Click to expand...
Click to expand...
 

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

Trouble calculating in a report b/c of data structure 0
What to choose for value in crosstab 2
Crosstab qry not adding up 1
Crosstab Query - Recalculate totals after each date 2
Crosstab - Multiple values on each row 5
Group by Range in Crosstab Query 5
Calculate percentage 8
Need to create totals using a Crosstab Query 3

Top