Crosstab Union Queries for Charts

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.


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).


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.


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.


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

Find or Filter Data on Form

Who is Online

Automated Email Alerts.shtml

Configure Outlook for Lotus Notes