Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Crosstab Union Queries for Charts

Introduction.

For preparing Charts in Excel, the Source Data keyed directly into cells or source-data cells are linked into specific locations on the Worksheets where summary figures are available. The arrangement of chart values are planned and entered in the desired order depending on the type of Chart we require. Charts are created out of them as the final step for taking print outs or presentation purposes. Most of the time this is a one-time exercise. If modifications like adding another month's data, if it is a month-wise chart, then we have to modify the physical arrangement of the data to add another set and change the chart Data Series Range Addresses to make the new data appear on the Chart. If advance planning is made for all twelve months data area for the Chart and if the actual data appears only up to March then balance 9 months Chart Area will be empty.


Preparing Data for Chart.

For preparing charts in MS-Access we need to work with Queries to shape the data into a form that can go directly into the Chart. In MS-Access, preparation of data can be automated by running Action Queries wherever needed through macros and it is only a one time set up. We can use the partially shaped Summary Data with other Queries, like Cross-tab and Union Type Queries to reformat them to make it suitable for Charts. This Type of Queries takes new rows of data when added to the Summary Source Table and reformats them into new columns reflecting that change automatically on the Charts as well. Once the Chart is created with these types of Queries it works every time without any modifications and your Chart is ready with new values in no time.

We will look into the usage of Cross-Tab and Union Queries in action, shaping the summary data suitable for Bar Chart or Line Chart. We assume that the Profitability Summary sample data of Branch1 shown below is prepared with the use of Action Queries, like Make-Table, Append Queries etc.

Table Name: Profitability
Location PYear PMth Revenue Expenses
Branch1 2008 1 $25,000 $5,000
Branch1 2008 2 $35,000 $7,500
Branch1 2008 3 $15,000 $4,000
Branch1 2008 4 $40,000 $15,000

The summary data above is reformatted for the Chart below with the help of Select, Cross-Tab and Union Queries. The Chart is directly linked with the Union Query that combines data from Revenue, Expenses and Income Values from their respective Cross-tab Queries.

When the summary table above is added with fresh data of May, June and others the change will automatically reflect on the chart without any changes to the source Queries of the Chart.

The first step that we have taken is to format the Year and Month values to create the last date of the month and to show that on the X-Axis of the Chart. For this we have created a Select type Query with the name Summary_inQ on the source data table Profitability above. The Query's SQL String is given below.

  1. Query Name: Summary_inQ

    SELECT Profitability.*,
     DateValue([pyear] & "-" & [pmth] & "-01") AS dt,
     DateAdd("m",1,[dt])-1 AS edate
    FROM Profitability;

    We have created expressions with two Date Functions on the above Query to calculate the last date of the month like 31/01/2008, 29/02/2008 and so on.

    DateValue([pyear] & "-" & [pmth] & "-01") AS dt

    This expression will create the Date 01-01-2008 from Year 2008 and month value 1 for January and the expression is named as dt.

    DateAdd("m",1,[dt])-1 AS edate

    This expression calculates the last date of the month using the column dt as input value and the resultant column is named as edate (for end date).


    Create the Query.

    Open a new Query in design view. Do not select any Table or Query from the displayed list. Display the SQL Window (select SQL View from View Menu), copy and paste the above SQL String into the Query's SQL edit window and save the Query with the name Summary_inQ.

    I have this urge for using the letter Q or the word Cross or Union with the name of the Queries, because these Query types can be used as source for other Queries and easy to identify them when the list is displayed mixed with table names.


    The Cross-Tab Queries.

    Next we will create three Cross-Tab Queries for the values Revenue, Expenses and Income figures. The SQL Strings are given below. Copy and paste each one into new Query's SQL window and save them with the name as shown.

  2. Query Name: Revenue_Cross
    TRANSFORM Sum(Summary_inQ.Revenue) AS SumOfRevenue
    SELECT "1Revenue" AS [Desc]
    FROM Summary_inQ
    GROUP BY "1Revenue"
    PIVOT Summary_inQ.edate;

    If you check the SELECT clause on the second line above I have prefixed the digit 1 with the word Revenue and the next two Queries that we are going to create will have 2 and 3 prefixed to the descritpion Expenses and Income respectively. When the Cross-Tab Query Values are combined with the Union Query they will be sorted in the correct order of Revenue, Expenses and Income and will appear in that order on the Chart, rather than Expenses on the top, Income in the middle and Revenue at the bottom, when sorted by Union Query in Alphabetical Order.

  3. Query Name: Expenses_Cross
    TRANSFORM Sum(Summary_inQ.Expenses) AS SumOfExpenses
    SELECT "2Expenses" AS [Desc]
    FROM Summary_inQ
    GROUP BY "2Expenses"
    PIVOT Summary_inQ.edate;
  4. Query Name: Income_Cross
    TRANSFORM Sum([Revenue]-[Expenses]) AS Expr1
    SELECT "3Income" AS [Desc]
    FROM Summary_inQ
    GROUP BY "3Income"
    PIVOT Summary_inQ.edate;

    Now, we have to combine the data from all the three Cross-Tab Queries into a Union Query and Design the Chart using the Union Query as Source. The Union Query SQL String is given below. Copy and Paste it into a new Query as we did for Cross-Tab Queries and save it with the name as suggested.

  5. Query Name: Union_4Chart

SELECT * FROM [Revenue_Cross]
UNION SELECT * FROM [Expenses_Cross]
UNION SELECT * FROM [Income_Cross];

The Summary Data travels all the way from the Profitability Table to the Union Query to the Chart. It takes different shapes and that shape changes when new month's data are added to the base table. The Chart responds to the change on the source Union Query and shows new values on the Chart automatically.


Designing the Chart.

Now, all you have to do is to design a Chart using the Union_4Chart Query. Instead of repeating the same procedure that I have already explained earlier you can go to the Next Page and continue there from Step-4 onwards. Change the Property Value Row Source = BarChartQ to Row Source = Union_4Chart and continue with creating the Chart and formatting it as explained there. Make changes to the Titles and other formatting options to make the finished Chart look like the sample given above.

Keyboard Shortcuts


  1. MS-Access and Graph Charts
  2. MS-Access and Graph Charts-2
  3. Working With Chart Object in VBA
  4. Column Chart and VBA
  5. Pie Chart Object and VBA
  6. Missing Lines in Line-Chart
  7. Pie Chart and Live Data on Form
  8. Scaling Chart Object
  9. Cross-Tab Union Queries for Chart
Share:

No comments:

Post a Comment

Comments are subject to moderation before publishing.

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

http://clicky.com/101199826
Blog Directory Visit blogadda.com to discover Indian blogs

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

Forms How Tos Functions MS-Access Security Reports msaccess forms Animations Class Module msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Array Queries msaccess reports External Links msaccess tips Accesstips Downloads Objects Property Event Menus and Toolbars Collection Object Controls MsaccessLinks Process Controls WithEvents Art Work msaccess How Tos Combo Boxes Graph Charts List Boxes VBA msaccessQuery Command Buttons Form Command Button Data Dictionary Object Emails and Alerts Query RaiseEvent Report Calculation Custom Wizards DOS Commands Data Type ms-access functions msaccess functions msaccess graphs msaccess reporttricks Custom Functions Item Object Reference msaccessprocess security advanced Access Security Add Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Copy Excel Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database function ms-access msaccess wizards reference text tutorial vba code Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Export External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Tables Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload

Featured Post

Call Function From MouseMove Event Property

Introduction. This is about running a User-Defined Function (say myFunction(Parameter) ) on the Mouse Move Event Property of Text Boxes.  W...

Labels

Blog Archive

Recent Posts