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

Crosstab Union Queries for Charts.

When preparing charts in Excel, source data is either entered directly into cells or linked to specific worksheet locations where summary figures are available. The arrangement of these values is planned in advance and entered in the required order, depending on the chart type. Charts are then created from this data as a final step, either for printing or presentation purposes.

In most cases, this is a one-time task. However, if updates are needed—such as adding another month’s data to a month-wise chart—you must adjust the physical arrangement of the data and update the chart’s Data Series Range so the new values appear.

If space for all twelve months is allocated in advance but actual data is only available up to March, the remaining nine months in the chart will appear blank.

Preparing Data for the Chart.

When preparing charts in MS Access, we first need to shape the data into a format that can be used directly in the chart. This is done using queries. The process can be automated by running action queries through macros, making it essentially a one-time setup.

Partially prepared summary data can then be further refined with other queries, such as crosstab or union queries, to format it for charting purposes. These queries can automatically transform newly added rows in the summary source table into new columns, ensuring that charts update instantly to reflect the changes.

Once a chart is created from such queries, it continues to work without further adjustments—your chart is always ready with updated values.

Next, we will explore how crosstab and union queries can be used to shape summary data for bar charts and line charts. For this example, we will assume that the Profitability Summary data for Branch 1, shown below, was prepared using action queries such as Make Table and Append queries.

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 using a combination of Select, Crosstab, and Union queries. In this setup, each Crosstab query extracts and structures the Revenue, Expenses, and Income values. These are then merged in a final Union query, which serves as the direct data source for the chart. This approach ensures the chart updates automatically whenever the underlying summary data changes.

Click to Enlarge

Step 1 – Create the `Summary_inQ` Query

The first step is to format the Year and Month values, calculate the last date of the month, and use this date on the chart’s X-axis.

We will create a Select query named Summary_inQ based on the Profitability table.

Open a new query in Design View.
Do not select any tables or queries from the displayed list.
From the View menu, choose SQL View.

Copy and paste the following SQL into the SQL editor:

sql

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

Save the query as Summary_inQ.
The Summary_inQ Query in DataSheet View is given below:

Tip: I like to include the letter Q in query names, or words like Cross or Union, to make them easy to identify when mixed in with table names.

When new data (e.g., for May, June, and later months) is added to the Profitability table, they will automatically appear in the Summary_inQ and the other Crosstab Queries.

Step 2 – Create the Crosstab Queries.

We will now create three Crosstab queries — one each for Revenue, Expenses, and Income.

Follow these steps for each Crosstab query:

Open a new query in Design View.
Do not select any tables or queries from the displayed list.
Switch to SQL View.
Copy and paste the SQL string for the query you are creating.
Save the query with the name shown.

1. Revenue Crosstab – `CrossRevenueQ`

sql
TRANSFORM Sum(Summary_inQ.Revenue) AS SumOfRevenue
SELECT "1Revenue" AS [Desc]
FROM Summary_inQ
GROUP BY "1Revenue"
PIVOT Summary_inQ.MonthLastDate;

2. Expenses Crosstab – `CrossExpensesQ`

sql
TRANSFORM Sum(Summary_inQ.Expenses) AS TotalExpenses
SELECT "2Expenses" AS Category
FROM Summary_inQ
GROUP BY "2Expenses"
PIVOT Summary_inQ.MonthLastDate;

3. Income Crosstab – `CrossIncomeQ`

sql
TRANSFORM Sum([Revenue]-[Expenses]) AS Expr1
SELECT "3Income" AS [Desc]
FROM Summary_inQ
GROUP BY "3Income"
PIVOT Summary_inQ.MonthLastDate;

Note on naming:
In the SELECT clause of each SQL statement, you will see a number prefixed to the category name:

1Revenue

2Expenses

3Income

This ensures that when the queries are combined in a Union query, the results are sorted in the logical order (Revenue → Expenses → Income) instead of alphabetical order, which would place Expenses first, Income in the middle, and Revenue last.

Step 3 – Create the Union Query

Finally, we will combine all three Crosstab queries into a single Union query, which will serve as the chart’s source.

Open a new query in Design View.
Do not select any tables or queries from the list.
Switch to SQL View.
Copy and paste the following SQL string into the SQL editor:

sql
SELECT * FROM CrossRevenueQ
UNION ALL
SELECT * FROM CrossExpensesQ
UNION ALL 
SELECT * FROM CrossIncomeQ;

Save the query with the suggested name — for example: UnionSummaryQ.
UnionSummaryQ Query output in Datasheet View is given below:

Designing the Chart.

Now, all that is left to do is to design a Chart using the UnionSummaryQ 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 = UnionSummaryQ 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.

LEARN MS-ACCESS TIPS AND TRICKS