Cross Tab Query with Calcuation & Dates Grouped by Quarter-Year

[ctfrigg]

Hi!

I need to create a cross-tab query and groups my dates by quarters-year and
calcuates a percentage based on my column "Receipt Type". I am not sure how
to do this.

The calculation is the sum of an $amount when Receipt type = AMEX/sum of
all $amounts (with all receipt types)
This is what I want the end result to be

Q1-08 Q2-08 Q3-08
Op Unit 98% 92% 95%

Here is what I have so far but not sure how to do the data groupings or do
the calcuation.

TRANSFORM Sum(CorpCardCompliance_USD_AirHotel_Detail.[USD Reimburse]) AS
[SumOfUSD Reimburse]
SELECT CorpCardCompliance_USD_AirHotel_Detail.OpUnitName
FROM CorpCardCompliance_USD_AirHotel_Detail
GROUP BY CorpCardCompliance_USD_AirHotel_Detail.OpUnitName
PIVOT CorpCardCompliance_USD_AirHotel_Detail.ReportSubmittedDate;

Thank you so much for the help.

 

[dhookom]

Why don't I see "Receipt Type" in your "what I want the end result to be" or
in the SQL view of your query?

Can you create a crosstab and then calculate the percents in a report?

Duane Hookom
MS Access MVP

 

[ctfrigg]

I didn't pull the receipt type into the query because it will be part of the
calculated field (I think)- it won't be seen in the actual results. I need to
pull these results into an excel dashboard so I can't use a report to
accomplish this.

Op Unit Nam Q1-08 Q2-08
US 98%
UK 95%
Australia 89%

These %'s are the sum of the transactions that have a receipt type of AMEX
divided by the sum of all transactions (regardless of the receipt type). This
is where I am hung up.

Could I use something like this? And if so, how do I change it so I am
getting the sum of [SumOfUSD Reimburse]?

TRANSFORM
Sum(IIf(CorpCardCompliance_USD_AirHotel_Detail.[ReceiptType]="AMEX",1,0)/Count([ReceiptType])

Thanks!

Why don't I see "Receipt Type" in your "what I want the end result to be" or
in the SQL view of your query?

Can you create a crosstab and then calculate the percents in a report?

Duane Hookom
MS Access MVP

Hi!

I need to create a cross-tab query and groups my dates by quarters-year and
calcuates a percentage based on my column "Receipt Type". I am not sure how
to do this.

The calculation is the sum of an $amount when Receipt type = AMEX/sum of
all $amounts (with all receipt types)
This is what I want the end result to be

Q1-08 Q2-08 Q3-08
Op Unit 98% 92% 95%

Here is what I have so far but not sure how to do the data groupings or do
the calcuation.

TRANSFORM Sum(CorpCardCompliance_USD_AirHotel_Detail.[USD Reimburse]) AS
[SumOfUSD Reimburse]
SELECT CorpCardCompliance_USD_AirHotel_Detail.OpUnitName
FROM CorpCardCompliance_USD_AirHotel_Detail
GROUP BY CorpCardCompliance_USD_AirHotel_Detail.OpUnitName
PIVOT CorpCardCompliance_USD_AirHotel_Detail.ReportSubmittedDate;

Thank you so much for the help.