Access 2003: Creating Object/File for A Report of the Pivot Table
-
Hi all,
I know that the View of a Pivot Table can be derived from the existing objects in Access 2003. But the format of the Pivot Table can not be saved in the Access 2003 or Excel 2003 program. I need an electronic file of the customerized Pivot Table to present in the Microsoft Word/Excel/Power Point.
Suppose a Master Table that has 3 fields - Field1 Field2 Field3
xx1(in group1) yy1 zz1
xx2(in group1) yy2 zz2
xx3(in-group1) yy3 zz3
xx1(in group2) yy1 zz4
xx2(in group2) yy2 zz5
xx3(in group2) yy3 zz6
xx1(in group3) yy1 zz7
xx2(in group3) yy2 zz8
xx3(in group3) yy3 zz9
I want to create a Pivot Table:
yy1 yy2 yy3 (in X-Axis)
(in Y-axis)
xx1 zz1 zz4 zz7
xx2 zz2 zz5 zz8
xx3 zz3 zz6 zz9
I am thinking to do the following steps for getting the above-mentioned Pivot Table:
1) From the Master Table, I do the Stored Procedure 3 times by querying xx1, xx2 and xx3 and save them.
2) Under the “Report” Object, can I create a Report of the Pivot Table from the 3 Stored Procedure tables that are put in the Relational Diagram? This is the step I do not know how to do it!!! Any ideas/suggestions to help me in this task? Please help and advise.
Thanks in advance,
Scott Chang
-
This was removed by the editor as SPAM
-
There are several issues with your request. First of all, pivot tables are generally used to summarize data based on aggregates such as sums and averages, from data stored in traditional relational formats.
However, it appears that you are storing your data in EAV format (i.e. one field holds the names of the data identifiers, and another field hold the values), which is OK for many purposes, but it creates serious presentation issues. It seems you want to convert your EAV format to a more traditional format of cols vs rows. You also need to ensure that each inner cell of your converted/pivoted data is unique, so that cells never "overlap" or hide hidden data.
Accomplishing this task is a bit complex, because standard pivoting functions (see below) were designed work with aggregated data, and not with unique EAV data points for each cell of the pivot table. However, you can still use the pivoting functions, by using aggregates such as MAX, MIN, etc, so pull out single values for each pivot cell, and achieve your result.
For most puposes, I find the T-SQL methods (using CASE statements) for building pivot tables to be complex, extremely unwieldy and grossly inadequate. If you want to try this approach, search BOL for "Pivot" and "CASE." I have used this approach many times, and I hate it. There are some SPs for this elsewhere on SqlServerCentral.com
The Office Web Components pivot tables in Access produce beautiful results (much better than Excel Pivot tables), but they are not easily saved in the PivotTable format, and thus can't be directly exported to Excel. In fact, I find that it is nearly impossible to work with then outside of Access!
Thus, there is no easy general solution to your problem using SQL Server or ADPs alone.
For creating complex pivot tables when the above solutions don't work, you can try:
1) Create a new Access MDB database and link to your MSS tables. Then you can use the JET Transform function (see the help file) to create and manipulate your Pivot tables. Once you have the results in a datasheet, you can copy and paste into Excel.
2) For more complex stuff, I use SQL-DMO and VBA to create new MSS tables and stuff the custom Pivot Tables using code. This is by far the most powerful approach, as you can produce any desired result. It just takes a more thought and time. However, once you have a few code templates, it works great.
3) There are many third party tools for creating pivots. Do a google search, and check out Tableau.
I doubt that these general remarks are going to be of much help - but I hope it gets you started on a solution that works for you.
-
Hi Richard,
Thank you very much for your valuable information and technical insight.
I am very new in doing Access 2003, SQL Server 2000/MSDE and VBA programming. But I have to have the object/file of a Report of the Pivot Table for my scientific presentations. So I spent last 10 days to search more information about SQL-DMO, Dynamic SQL/Cross-Tabs/Pivot Tables and How to Rotate a Table in SQL Server. I found the following short article from http://support.microsoft.com/default.aspx?scid=kb;EN-US;q175574:
HOW TO: Rotate a Table in SQL Server
Article ID : 175574 Last Review : February 14, 2005 Revision : 3.1 This article was previously published under Q175574SUMMARY
This article describes how to rotate a SQL Server table. Suppose you have a table that is named QTRSALES. The table has the columns YEAR, QUARTER, and AMOUNT with the data in the following format (note that there is no row for the fourth quarter of 1996):Year Quarter Amount ------------------------------- 1995 1 125,000.90 1995 2 136,000.75 1995 3 212,000.34 1995 4 328,000.82 1996 3 728,000.35 1996 2 422,000.13 1996 1 328,000.82
Now, suppose you want to rotate the table so that you can see the data in the following format:
YEAR Q1 Q2 Q3 Q4 ------------------------------------------------------------------- 1995 125,000.90 136,000.75 212,000.34 328,000.82 1996 328,000.82 422,000.13 728,000.35 0.00
The query that you would use to rotate the table is in the next section of this article.
Sample Query to Rotate the Table
Here is the query that you would use to rotate the table:
SELECT YEAR, Q1= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 1 AND YEAR = Q.YEAR),0), Q2= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 2 AND YEAR = Q.YEAR),0), Q3= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 3 AND YEAR = Q.YEAR),0), Q4= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 4 AND YEAR = Q.YEAR),0) FROM QTRSALES Q GROUP BY YEAR
Query for Large Tables
For large tables, this query will be faster:
year=q.year,SUM(CASE quarter WHEN 1 THEN amount ELSE 0 END) as Q1,SUM(CASE quarter WHEN 2 THEN amount ELSE 0 END) as Q2,SUM(CASE quarter WHEN 3 THEN amount ELSE 0 END) as Q3,SUM(CASE quarter WHEN 4 THEN amount ELSE 0 END) as Q4FROM qtrsales qGROUP BY year
REFERENCES
APPLIES TO
• Microsoft SQL Server 6.0 Standard Edition • Microsoft SQL Server 6.5 Standard Edition • Microsoft SQL Server 7.0 Standard Edition • Microsoft SQL Server 2000 Standard Edition Keywords:
kbhowto kbhowtomaster kbusage KB175574 ////////////////////////////////////////////////
This is exactly what I need for my Pivot Table.
I want to incorporate the above-mentioned "Rotate a Table" query into my following Access 2003 adp:
Sub RotateTableInSQLDB()
Dim cnn As Connection
Dim rst As Recordset
Dim str As String
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim Msg As String
Dim QTRSALES As Object
'Create a Connection object after instantiating it,
'this time to a SQL Server database.
Set cnn = New ADODB.Connection
cnn.Open "Provider=SQLOLEDB;Data Source=<myComputerName>;" & _
"Initial Catalog=adp1SQL;Integrated Security=SSPI;"
'Create recordset reference, and set its properties.
Set rs = New ADODB.Recordset
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
'Open recordset, and print some test records.
rs.Open "QTRSALES", cnn
Set cmd = New ADODB.Command
'Specify the Query
cmd.CommandText = "SELECT * FROM QTRSALES"
cmd.CommandType = adCmdText
Set cmd.ActiveConnection = CurrentProject.Connection
Set rs = cmd.Execute(NumRecs)
'Loop Through and Display The Field Names
Msg = " "
For i = 0 To rs.Fields.Count - 1
Msg = Msg & "|" & rs.Fields(i).Name
Next
MsgBox Msg
'Loop Through and Display The Field Values for Each Record
Msg = " "
Debug.Print rs.Fields(0).Name; Spc(10); rs.Fields(1).Name; Spc(6); rs.Fields(2).Name
rs.MoveFirst
Do While (Not rs.EOF)
If rs.Fields(0).Value = "1995" Or "1996" Then
Debug.Print rs.Fields(0).Value, rs.Fields(1).Value, rs.Fields(2).Value
End If
rs.MoveNext
Loop
MsgBox ("Connection was successful.")
'Clean up objects.
rs.Close
'rst.Close
cnn.Close
Set rs = Nothing
Set rst = Nothing
Set cnn = Nothing
End Sub
========================
In the above VBA code (in Module), I try to use the Command Object 'QTRSALES' and insert the "SELECT YEAR" statement after the "Set rs = cmd.Execute(NumRecs)" statement and I get a Compile error: Expected: Case.
I am completely lost in this project. Please help and answer the following things:
(1) How can I incorporate the code statements of the "Sample Query to Rotate the Table" into my Source Code? Where should I start inserting?
(2) I want to get the "rotated" result saved in the "Table" or "Report". What kind of cmd commands should I use to acieve it?
(3) Please tell me what "EAV format/data" and ""MSS table" are.
Thanks again,
Scott Chang
-
In the above VBA code (in Module),
I try to use the Command Object 'QTRSALES' and insert the "SELECT YEAR" statement after the "Set rs = cmd.Execute(NumRecs)" statement and I get a Compile error: Expected: Case.
I am completely lost in this project. Please help and answer the following things:
(1) How can I incorporate the code statements of the "Sample Query to Rotate the Table" into my Source Code? Where should I start inserting?
(2) I want to get the "rotated" result saved in the "Table" or "Report". What kind of cmd commands should I use to acieve it?
(3) Please tell me what "EAV format/data" and ""MSS table" are.
----------------------------------------
Hi Scott,
I have a few comments:
1) If you have already created a working stored procedure with CASE statements, you don't need any code to view it.
Just locate the SP in the ADP's database window and double click it. You will see a datasheet with all your data.
You can then copy the entire contents, and paste it into Excel, or whatever you want.
You can do the same thing in Enterprise Manager, or Query Analyzer. Just cut and paste.
If the SP is working, you can just use it as the data source for any report.
2) The T-SQL CASE statements go into a stored procedure or function in SQL Server. You can then call the SP/function in Access,
or base a report or form on it, or just view the results directly (by double clicking) in the database window.
3) EAV= "Entity-Attribute-Value" means that each unique datum (Entity) is identified by an Attribute (the name of the data type), and its value.
In a normal relational-style table, yoiu would have one column for each Atribute.
However, sometimes you don't have a firm handle on all the different types of attributes (such as store merchandise, or lab tests),
so its impossible to create a static table structure for your data.
In this case, you can create a column in your table to hold the attribute name, and another to hold the value.
As you have discovered, one problem with this is presenting the attributes as columns instead of rows.
You need to pivot the data to see the traditional format with attributes as columns.
MSS=Microsoft SQL Server
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply