Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Crosstab Union Queries for Charts


For preparing Charts in Excel, the Source Data keyed directly into cells or source-data cells are linked to specific locations on the Worksheets where summary figures are available. The arrangement of chart values is 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 printouts 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 the balance of 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, the preparation of data can be automated by running Action Queries wherever needed through macros and it is only a one-time setup. We can use the partially shaped Summary Data with other Queries, like Cross-tab and Union Type Queries to reformat them to make them suitable for Charts. 

This Type of Query 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 to 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, create the last date of the month, and 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 the 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 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 the 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 the 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 description 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 a 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 the new month's data are added to the base table. The Chart responds to the change to the source Union Query and it shows the 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.

  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

No comments:

Post a Comment

Comments subject to moderation before publishing.




Post Feed


Popular Posts

Blog Archive

Powered by Blogger.


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