Crosstab Union Queries for Charts

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.

1. Open a new query in Design View.

2. Do not select any tables or queries from the displayed list.

3. From the View menu, choose SQL View.

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

5. Save the query as Summary_inQ.

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

1. Open a new query in Design View.

2. Do not select any tables or queries from the displayed list.

3. Switch to SQL View.

4. Copy and paste the SQL string for the query you are creating.

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

1. Open a new query in Design View.

2. Do not select any tables or queries from the list.

3. Switch to SQL View.

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

5. Save the query with the suggested name — for example: UnionSummaryQ.

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

1. MS-Access and Graph Charts
2. MS-Access and Graph Charts-2
3. Working With Chart Objects 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

Read More

Union Query

Introduction.

Union Queries are useful for combining data from multiple tables or queries while keeping the original data physically separate.

For example, data tables from different branches (Branch 1, Branch 2, and Branch 3) are linked directly into our main system as separate files. Our task is to combine their contents to prepare monthly reports, charts, and other summaries.

One approach is to use Append Queries to merge all the data into a single table before processing. However, that creates duplication, as we’re storing the same data in two places. A better option is to use a Union Query, which combines the data virtually, without altering or duplicating the source tables. This way, branch locations can continue to update their own files independently, while our combined view remains up to date.

Unlike other queries, we cannot drag and drop tables or queries onto the Query Design grid when creating a Union Query. Instead, we must write the SQL statement manually in the SQL View. Fortunately, the syntax is straightforward—you just need a little practice to get comfortable with it.

Before we proceed, let’s look at some sample tables, including their contents, number of fields, field names, values, and data types.

Sample Tables

Table 1 Image:

Click to Enlarge

Table 2 Image:

Click to Enlarge

Creating a Union Query

Let us look at an example. The contents of the above tables can be combined in a Union Query.

1. In the Database window, click Queries under Objects, and then click New on the Database window toolbar.

2. In the New Query dialog box, click Design View, and then click OK.

3. Without adding tables or queries, click Close in the Show Table dialog box.

4. On the Query menu, point to SQL Specific, and then click Union.

5. Enter SQL SELECT statements to combine data from tables or queries.

Example:

TABLE [Branch1] UNION SELECT * FROM [Branch2];

OR

SELECT * FROM [Branchl] UNION SELECT * FROM [Branch2];

Copy and paste one of the above SQL strings into the SQL design window and save the Query with the name UNION1 or with any name you prefer. Open the Union Query in the normal datasheet view.

The output of the above query is given below:

Click to Enlarge

What Does the Union Query Do?

The above SQL statement combines data from both the Branch1 and Branch2 tables, sorting the results by the first field. Any duplicate records found in the combined data will be removed from the output.

If you have more than two tables or queries, you can extend the statement by repeating the part:

sql
UNION SELECT * FROM [xxxxxxxx]

Here, [xxxxxxxx] represents the name of the additional table or query. Continue adding these parts for each source, and place a semicolon (;) at the very end of the statement.

In simple terms, the SQL statement can be interpreted as follows:

Take all field values from all records in the Branch1 table, then take all field values from all records in the Branch2 table. Combine the results, remove any duplicates, and sort the records in ascending order based on the values in the first column.

Note that you cannot use fields with the Memo, OLE, or Hyperlink data types in the source tables or queries for this type of Union Query.

However, if you insert the keyword ALL immediately after the word UNION, the behavior changes:

• All field types (including Memo, OLE, and Hyperlink) are allowed.

• No sorting is performed.

• Duplicate records are retained.

• The records are returned in the order in which the source tables or queries are listed in your SQL statement.

An Experiment.

Let’s try another experiment. Instead of joining a different table, we will use the contents of the same table twice and observe the result. Modify the SQL string as follows, or create a new query with this statement:

sql
SELECT * FROM Branch1  
UNION  
SELECT * FROM Branch1;

Open the query in Datasheet View. What do you see? The second set of records is gone. That’s because a Union Query automatically removes duplicate records from its output unless told otherwise.

Now, modify the SQL string by adding our magic word ALL immediately after the word UNION:

sql
SELECT * FROM Branch1  
UNION ALL  
SELECT * FROM Branch1;

Open the query again in Datasheet View. This time, the table contents will appear twice in the output because duplicates are no longer suppressed.

Points to Note.,

A Union Query has certain rules that cannot be bypassed. For example:

• All source tables or queries must have the same number of fields.

• Any sorting must be done using field names from the first table or query in the statement.

These requirements are built into how Union Queries work—there is no magic word or keyword that can override them.

If the fields in your source tables or queries are arranged in a different order, or if the data types in corresponding positions differ (for example, Number in one table and Text in the other), the Union Query will still return the results in the order you specified. However, the column headings will always be taken from the first table or query in the statement. If the field order is incorrect in any of the sources, you may not notice it immediately—the mismatch often becomes apparent only when you carefully review the output. 

If you look at the sample output above, you’ll notice that the Age and Country values from Branch2 are appearing under the Salary and Address columns.

To see just how easily this kind of mismatch can happen, let’s make a small change to the structure of the second table (Branch2) and observe the output again. This will help us understand what can go wrong when we’re not careful in handling a Union Query.

1. Open Table Branch2 in the design view.
2. Click and drag the field Age and place it after the field FirstName.
3. Click and drag the Country field and place it next to the field Age.
4. Save the Table Structure.
5. Open the Union Query and inspect the output.

The shifted field values will appear exactly in their new positions, moving other values forward from the point where we removed the Age and Country fields to the left. This happens without considering the data types of the other fields in the Union Query, and no warning messages will be displayed.

In our earlier example, we selected all the fields from the member tables by using the * symbol after the SELECT clause. If you only need specific fields from the member tables or queries, you must list them explicitly and in the correct order.

For example, to select EmployeeID, FirstName, and BirthDate from both tables and display the results sorted by 'FirstName', We can write the SQL string as shown below:

SELECT [EmployeelD],
 [FirstName],
 [BirthDate]FROM [Branch1]
UNION ALL SELECT [ID],
 [FirstName],
 [BirthDate] 
FROM [Branch2]
ORDER BY [FirstName];

The 'ORDER BY' Clause can reference only the field names from the source object used in the first SELECT statement; otherwise, an error will occur when you attempt to view the results.

If you open Table Branch2 in Datasheet View, you can select and drag the Age and Address columns to any position for viewing purposes. If you save the layout, after making these changes, the columns will always appear in their rearranged positions in Datasheet View, regardless of their actual physical order in the table structure or query design.

Important: Never rely on the Datasheet View of a table or query to determine the field layout for a Union Query. Always refer to the actual table structure or the Select Query Design View to confirm the field order before adding them to the Union Query, and ensure that they appear in the same order in all member queries.

CAUTION.

When you are designing Union Queries having hundreds or thousands of records in the output, always make a point to check through the object-level record set, at least once, to ensure that they are in the correct order. Once we are sure that the output is listed correctly, then sort, suppress duplicates, or apply conditions to filter the data.

When applying conditions, you must specify Criteria at each Table or Query level.

For example, if you want to select employees born on 31st December 1960 or earlier from both tables, then you will write the SQL in the following manner:

SELECT * from [Branch1]
WHERE ([BirthDate] <=#12/31/1960#)
UNION ALL SELECT * from [Branch2] 
WHERE ([BirthDate] <=#12/31/1960#);

This will take records from each member table or query that satisfies the criteria. Remove the WHERE clause from any one position and check the output.

• Find or Filter Data on Form
• Who is Online
• Automated Email Alerts/
• Configure Outlook for Lotus Notes
• MS-Access and Email

Read More

Percentage on Total Query

Calculating Line Value Percentage on Total.

To calculate the percentage contribution of each value in a row based on the total of all rows, let’s work through an example.

1. Assume that Northwind Traders stores customer-wise, order-wise sales figures in a table. Management now wants a customer-wise sales summary and has further requested the following: 

2. Top 10 Customers Only – Display only those customers with the highest sales values.

We can solve this easily with a Total Query.

Steps:

1. Import the Orders and Customers tables from the Northwind.mdb sample database.

   • If you’re unsure where the sample database is stored on your machine, refer to the instructions on the Saving Data on Forms Not in Table page for location details.

2. Create a Total Query to sum the sales per customer.

3. Calculate each customer’s percentage by dividing their sales value by the total sales of all customers.

4. Apply a sort to display results in descending order and limit the output to the Top 10 customers.

With that, you’ll have a clear view of the top customers and their share of the total sales.

The Query's SQL Statement.

For our task, we only need the Orders table. The Customers table serves as the source for a combo box in the Orders table, allowing us to enter the CustomerID. If we omit the Customers table, an error message will appear when we use the Orders table in our query.

To proceed, open the SQL (Query) editing window in your project (without selecting any table or query). Then, copy the following SQL statement and paste it into the SQL editing window:

1. SELECT TOP 10 Orders.CustomerID,
      Sum(Orders.Freight) AS Sales,
      DSum("freight","orders") AS Total,
      Sum([freight]/DSum("freight","orders")) AS Percentage
   FROM Orders
   GROUP BY Orders.CustomerID
   ORDER BY Sum(Orders.Freight) DESC;

2. Switch the query from SQL View to Design View (View → Design View) to see the layout visually.

   We have grouped the records by CustomerID, summed the Freight values for each customer, and renamed this column Sales.

   The third column calculates the Total Freight Value for all records in the Orders table using the DSum() function. Note that in the Total row, we have used Expression instead of Sum.

   To calculate the percentage of each customer’s sales against the total sales, we repeat the Sum() and DSum() functions in the formula:

   css
   Percentage: Sum([Freight]) / DSum("Freight", "Orders")
   

   Even though we have already calculated Sales (customer-wise total) and Total (overall freight value) in earlier columns, we cannot simply use 'Sales / Total' In this query. Total Queries do not allow referencing calculated field names directly in the same query.

   If you find the percentage formula too cumbersome, you can save this query without the Percentage column, then create a second query using the first one as its source. In the second query, you can simply write:

   makefile
   Percentage: Sales / Total
   

   This time, it will work without issue.

   Finally, to format the Percentage column, click anywhere in the column, open the Property Sheet (View → Properties), set Format to Percentage, and set Decimal Places to 2.

3. Listing the Top 10 highest Percentage Records.

4. To limit the output of the Query to 10 Records with the highest Sales values, we have to change the Top Values Property of the Query. Click on an empty area somewhere near the Table above.

5. Display the Property Sheet (View -> Properties) if you have closed it.

6. Change the Property Top Values to the desired value. Currently, it will be 10, because we have mentioned SELECT TOP 10 in the SQL String that you have pasted into the SQL Window.

7. Open the Query in Normal Datasheet View and check the Result. We have already set the Sorting order to descending under the Sales Column so that the Highest Sales Value and Percentage will be at the top Row.

Earlier Post Link References:

• Reminder PopUps
• MS-Access Graph Charts
• MS-Access Graph Charts-2
• Report Page Border
• Highlighting Reports

• Who is Online
• Automated Email Alerts/
• Configure Outlook for Lotus Notes
• MS-Access and Email
• Dynamic Report

Read More