Crosstab Union Queries for Charts ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

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.

Click to Enlarge

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.

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

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;

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