Solved

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

Posted on 2009-07-10
10
376 Views
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.
Query14.doc
0
Comment
Question by:Jsara
  • 6
  • 3
10 Comments
 
LVL 2

Expert Comment

by:billb1057
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.
0
 

Author Comment

by:Jsara
ID: 24826609
How to get the totals beneath each supervisor. Can you please help me with the code.
0
 
LVL 2

Accepted Solution

by:
billb1057 earned 500 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"

Union

SELECT tbl_supervisors.supervisor, tbl_supervisors.name, 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

0
 
LVL 2

Expert Comment

by:billb1057
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.
0
 
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?

JeffCoachman
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:Jsara
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.
0
 
LVL 2

Expert Comment

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

Author Comment

by:Jsara
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;

Query15.doc
0
 
LVL 2

Expert Comment

by:billb1057
ID: 24850582
That worked for me.  What happens when you try it?
0
 
LVL 2

Expert Comment

by:billb1057
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.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now