Printer Friendly

Stacked column chart.

A stacked column or bar chart is a good way to illustrate how values (like monthly sales totals) break down by category. In the database structure, however, category and amount are stored in different rows, not different columns (fields). A crosstab query can be used to pivot, or transpose, the information and enable the results to be graphed.


You can use the query wizard to create a crosstab query, but we will build the query from scratch so we can combine the tables and pivot in the same step. Make a new query. Choose all three tables. Relationships are defined between the tables, as shown in Figure 1. The ProdCats (product categories) table contains records for Brass, Keyboard, Percussion, String, and Wind. In the Products table, each record has the product name and a category. The sales of those products are stored in the ProdSales table. The date of the sale is dtSale, and the revenue is called Amount. All three tables include fields with "ID" as part of the name. These are the primary or foreign keys.

Change the query type by right-clicking in the gray area of the query design and choosing Query Type, Crosstab Query from the shortcut menu. You can also change the type by going to the Design ribbon tab and selecting Crosstab in the Query Type group.

Enter EOMSale: DateSerial(Year ([dtSale]),Month([dtSale])+1,0) into the first field. The DateSerial function needs three numbers, which represent year, month, and day. For the year, Year ([dtSale]) returns the year from the date of sale. Month([dtSale]) does the same for the month, to which we're adding one (1). We're using a zero (0) for the day because "zero day" of the next month is, mathematically, equal to the last day of the current month. The Total line for this field should be set to Group By, and the Crosstab value is "Row Heading."

The field in the second column is ProdCat, with the Total line set to Group By, and the Crosstab value is "Column Heading." The third field is Amount. The Total is Sum, and the Crosstab is "Value."

Save the query and call it "qSales_ byCat_Crosstab." When you switch to Datasheet view, you will see that the product category names (ProdCat) have become column names. This provides a lot of flexibility.


Crosstab queries are extremely useful, but there are restrictions when using them for record sources. Because column names are dynamic, Access doesn't know what to do with a crosstab query used as a source unless the column names are specified.

With the query in Design view, click on a gray area of the query design. Display the Property Sheet. Clicking on the gray area should ensure that the selection type is Query Properties. Enter "Keyboard", "String", "Percussion", "Brass", "Wind" as the column headings. Switch to Datasheet view, and you will see that the columns appear in the same order. This query will be the row source for a chart. Save and close.


The resulting SQL statement for the crosstab query is:
TRANSFORM Sum(ProdSales.Amount) AS SumOfAmount
SELECT DateSeriaKYear([dtSale]),Month([dtSale])+1,0) AS
FROM (ProdCats
   INNER JOIN Products
      ON ProdCats.ProdCatID = Products.ProdCatID)
    INNER JOIN ProdSales
      ON Products.ProductID = ProdSales.ProductID
GROUP BY DateSerial(Year([dtSale]),Month([dtSale])+1,0)
PIVOT ProdCats.ProdCat In ("Keyboard","StringM,


Create a new form using Form Design. In Design view, create a chart object. (Choose Chart from the controls group of the Form Design Tools Design ribbon tab. Click through the questions of the wizard-the answers don't matter as all of that will be changed.) Once the chart is placed, set its Row Source on the Property Sheet to qSales_byCat_Crosstab.

Double-click on the chart object to begin working on it. Right-click within the chart area (but not on a specific object), and choose Chart Type. In the dialog box, select Column, Stacked Column (the second subtype in the top row). Click OK to close the dialog box. Right-click on the chart legend and select Format Legend. On the Placement tab, select Bottom. Click OK.

Click outside the hatched chart area to temporarily quit editing the chart. Change to Form view, save the form using an appropriate name. Return to Design view. With the chart now rendered properly, you can use the resizing handles to make it bigger. If needed, you can also resize the form canvas by placing the mouse on the bottom or right boundary. When the mouse cursor changes to a double-headed arrow with a line through it, click and drag the boundaries to resize. To change the design of the chart itself, e.g. colors, formatting, and other options, double-click the chart object to return to chart editing. See Figure 2 for an example of a stacked chart.

Next month, we will create a grouped report with subtotals and grand totals.



Crystal Long teaches and develops with Microsoft Access, Excel, and Office and specializes in remote training. She connects and helps as your project is built. For information on services or to share your thoughts and questions, e-mail crystal@ and put "SF" in the subject line.
COPYRIGHT 2016 Institute of Management Accountants
No portion of this article can be reproduced without the express written permission from the copyright holder.
Copyright 2016 Gale, Cengage Learning. All rights reserved.

Article Details
Printer friendly Cite/link Email Feedback
Title Annotation:ACCESS
Author:Long, Crystal
Publication:Strategic Finance
Article Type:Column
Geographic Code:1USA
Date:Apr 1, 2016
Previous Article:Growing with confidence and integrity.
Next Article:Advancing careers, making a difference: CMAs throughout the world often share their stories about their road to achieving the certification and how...

Terms of use | Privacy policy | Copyright © 2019 Farlex, Inc. | Feedback | For webmasters