crosstab export with grand totals to Powerpoint 1

[TomYC]

I have a simple crosstab that I use as the basis of a report; the report creates column totals (down) and row totals (across), giving me a grand total as well.
My request is to get the results of the report into an especially formatted Powerpoint slide.

I haven't succeeded in merely exporting the crosstab, and then manipulating it in excel to get my various totals; if I had, I might have let the Powerpoint slide accept that as a paste link or import.

Here is the SQL:
TRANSFORM Count(qr_detail_TY.id) AS [Total of ID]
SELECT qr_detail_TY.topic, qr_detail_TY.order, qr_detail_TY.issue
FROM qr_detail_TY
GROUP BY qr_detail_TY.topic, qr_detail_TY.order, qr_detail_TY.issue
ORDER BY qr_detail_TY.order
PIVOT qr_detail_TY.eval_txt In (Yes,No,"N/A");

You can imagine that I want this to be a recurring slide, e.g., each week, running this query and presenting the totals in the same, formatted way (number of rows may change, but not columns).

Thank you!
Tom

 

[dhookom]

Modify your first crosstab as follows to get the Row totals:

TRANSFORM Count(qr_detail_TY.id) AS [Total of ID]
SELECT qr_detail_TY.topic, qr_detail_TY.order, qr_detail_TY.issue, Count(qr_detail_TY.id) AS [RowTotal]
FROM qr_detail_TY
GROUP BY qr_detail_TY.topic, qr_detail_TY.order, qr_detail_TY.issue
ORDER BY qr_detail_TY.order
PIVOT qr_detail_TY.eval_txt In (Yes,No,"N/A");

Then to get column totals try a crosstab that returns a single record like:

TRANSFORM Count(qr_detail_TY.id) AS [Total of ID]
SELECT "All Topics" as Tops, Null As Ordr, Null as Iss, Count(qr_detail_TY.id) AS [RowTotal]
FROM qr_detail_TY
GROUP BY "All Topics"
PIVOT qr_detail_TY.eval_txt In (Yes,No,"N/A");

Then create a union query based on the two crosstabs.

Duane
Hook'D on Access
MS Access MVP

 

[TomYC]

Thanks, dhookom, for such clarity!
And I'm also learning my way through creating the Union Query, as well as arranging the columns so that my result will look like what would be expected in a spreadsheet design.

So I took your first code and (slightly modified) named it as Row Total, and the second one as Grand Total.

One question about sorting and ordering remains: in the first query, "ORDER BY qr_detail_TY.order" is just a field ("order") in the original permitting me to order the rows
("qr_detail_TY.issue") the way I want to, and not merely haphazardly or alphabetically. I don't really want to display a column of 1,2,3, etc. Sooo, is there any other way to control the sort order, in my Union Query? I guess that's a general question as well.

Here is the code I have now:

SELECT qr_summary_TY_RowTotal.issue, qr_summary_TY_RowTotal.[Yes], qr_summary_TY_RowTotal.[No], qr_summary_TY_RowTotal.[N/A], qr_summary_TY_RowTotal.RowTotal
FROM qr_summary_TY_RowTotal
GROUP BY qr_summary_TY_RowTotal.issue, qr_summary_TY_RowTotal.[Yes], qr_summary_TY_RowTotal.[No], qr_summary_TY_RowTotal.[N/A], qr_summary_TY_RowTotal.RowTotal

UNION ALL SELECT
q_Summary_TY_GrandTotal.Tops, q_Summary_TY_GrandTotal.[Yes], q_Summary_TY_GrandTotal.[No],q_Summary_TY_GrandTotal.[N/A], q_Summary_TY_GrandTotal.[RowTotal]
FROM q_Summary_TY_GrandTotal
GROUP BY q_Summary_TY_GrandTotal.Tops, q_Summary_TY_GrandTotal.[Yes], q_Summary_TY_GrandTotal.[No],q_Summary_TY_GrandTotal.[N/A], q_Summary_TY_GrandTotal.[RowTotal]
;
P.S. I hope that start shows up; it was well-deserved!

 

[dhookom]

You can add a new row heading in the crosstabs that identifies the crosstab source. The first might have a column like:
[tt]
Field: Source:"Details"
Total: Group By
Crosstab: Row Heading
[/tt]
The second would be:
[tt]
Field: Source:"Totals"
Total: Group By
Crosstab: Row Heading
[/tt]
Your union query could then finish with a sorting by Source and then another field or fields of your choice.


Duane
Hook'D on Access
MS Access MVP