Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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

Posted on 2009-07-10
Medium Priority
Last Modified: 2012-05-07
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.
Question by:Jsara
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3

Expert Comment

ID: 24826578
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.

Author Comment

ID: 24826609
How to get the totals beneath each supervisor. Can you please help me with the code.

Accepted Solution

billb1057 earned 2000 total points
ID: 24826751
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

Command Line Tips and Tricks

The command line is a powerful tool at the disposal of every Linux user. Although Linux distros come with beautiful user interfaces, it's worthwhile to learn the command line because it allows you to do a number of things that you otherwise cannot do from the GUI.  


Expert Comment

ID: 24829005
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.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24829700
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?


Author Comment

ID: 24849796
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.

Expert Comment

ID: 24849983
Jsara -- you can post the db or the SQL.

Author Comment

ID: 24850242
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;


Expert Comment

ID: 24850582
That worked for me.  What happens when you try it?

Expert Comment

ID: 24850653
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.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question