Need output in the attached format and finally to be exported to Excel

Need output in the attached format and finally to be exported to Excel
Current result is obtained using the cross tab query in MS Access.
Expected result should also include totals for each row grouped by Supervisor and the grand total at the end.

So that finally it can be generated in Excel.
Who is Participating?
billb1057Connect With a Mentor Commented:
Try this.
I called the original table tbl_supervisors

SELECT tbl_supervisors.supervisor, "Total" AS Sname, Sum(tbl_supervisors.Total) AS SumOfTotal, Sum(tbl_supervisors.Categ1) AS SumOfCateg1, Sum(tbl_supervisors.[Categ 2]) AS [SumOfCateg 2], Sum(tbl_supervisors.[Categ 3]) AS [SumOfCateg 3], Sum(tbl_supervisors.[Categ 4]) AS [SumOfCateg 4], Sum(tbl_supervisors.[Categ 5]) AS [SumOfCateg 5], Sum(tbl_supervisors.[Categ 6]) AS [SumOfCateg 6], Sum(tbl_supervisors.[Categ 7]) AS [SumOfCateg 7]
FROM tbl_supervisors
GROUP BY tbl_supervisors.supervisor, "Total"
SELECT tbl_supervisors.supervisor,, tbl_supervisors.Total, tbl_supervisors.Categ1, tbl_supervisors.[Categ 2], tbl_supervisors.[Categ 3], tbl_supervisors.[Categ 4], tbl_supervisors.[Categ 5], tbl_supervisors.[Categ 6], tbl_supervisors.[Categ 7]
FROM tbl_supervisors;

Open in new window

You could do it with nested queries.
First sum all the supervisors.
Then use that as a table to sumarize at the supervisor code setting.
Then union or append the results.
JsaraAuthor Commented:
How to get the totals beneath each supervisor. Can you please help me with the code.
Never miss a deadline with

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

You can just replace the "tbl_supervisors" with the name of your cross-tab query.
Also, when you're done -- use the Transfer Spreadsheet method to export the final result to Excel.
Let me know what you think -- it worked fine for me.
Jeffrey CoachmanMIS LiasonCommented:
1. This can be done in Access, as billb1057 has shown.
So the obvious question is, why can't this stay in Access?

2. When you export this to Excel the totals will be "Hard-Coded" (Not formulas)
So if you ever want to change a value, you will have to delete the Hardcoded value and insert a formula.
So why not just import the raw data and do the formula in Excel straight away?

JsaraAuthor Commented:
I tried billb1057' solution - not getting the expected results, not sure where I am going wrong.
Can a sample of access db posted.

To answer - boag2000: I am not familiar with excel much - could you post the working exampl.
Jsara -- you can post the db or the SQL.
JsaraAuthor Commented:
SELECT test2.supervisor, "Total" AS Sname, Sum(test2.Total) AS SumOfTotal, Sum(test2.[5779]) AS SumOf5779, Sum(test2.ERU) AS [SumOfERU], Sum(test2.FA) AS [SumOfFA], Sum(test2.LIM) AS [SumOfLIM], Sum(test2.NFA) AS SumOfNFA, Sum(test2.TA) AS SumOfTA, Sum(test2.UF) AS [SumOfUF]
FROM test2
GROUP BY test2.supervisor, "Total"
UNION SELECT test2.[supervisor], test2.[name], test2.Total, test2.[5779], test2.[ERU], test2.[FA], test2.[LIM], test2.[NFA], test2.[TA], test2.[UF]
FROM test2;

That worked for me.  What happens when you try it?
Ok, the one difference my be that this SQL assumes that "test2" is a table.  The problem might be because you're running it off of a crosstab query first (which is already summing columns).
One option might be to create a make-table query first and then run this SQL on it.
I'll check to see what happens with a crosstab.
All Courses

From novice to tech pro — start learning today.