Understand the elements in a crosstab report query

When trying to create a report based on a crosstab query, it can be quite intimidating trying to understand the elements used. So, as an example, let's say that you want a report of salespeople (going down along the left margin) showing their sales by month going across the page from left to right.

Salesperson Jan Feb Mar Apr
Adams 1,000 500 300 0
Brown 3,000 1,000 600
etc.

Here are the 3 elements needed for each crosstab query:
Row Heading - that would be "salesperson"
Column Heading - the month (date datatype)
Value - the monthly sales amount

Now that you understand the elements of a crosstab query, you can create one with a wizard.

Steps to create a crosstab query:
1) Click on Queries (left side of Access window), then select "New" from the toolbar. Select "Crosstab Query Wizard", then click the "OK" button.

2) The wizard will now ask for the name of the table containing the fields you want for the results. Select the appropriate table or query, then click "Next".

3) The next wizard window will ask for the name of the field(s) to be used as the "row heading". From our example above, we select "salesperson". We can select up to 3 fields at this time, but we can add more later when we work with the actual query. The additional fields should somehow be related to the salesperson, such as "sales_region", or "cost_center", or "SSN", etc. Got the point? After selecting the necessary fields, click "Next".

4) The next wizard window will want to know which field to use as the column heading. In our example above, this would be the month. Be aware that you may not have the months specifically designated, since they may exist within a regular date field. Later, we can use a function to retrieve the actual date. In the meantime, select the most appropriate field, then click "Next".

5) The wizard will now ask for the field to be used to calculate the column and row intersection. In our example above, this is the "value" or monthly sales amount. Depending on the type of field you select, different options will appear. For instance, if you pick a string type field, you will get this list of functions: "Count", "First", "Last", "Max", "Min". If the field type is numeric, you will get the same as the string types, plus "Avg", "StdDev", "Sum", and "Var". Most times you will use "Sum". This window also has the option of getting "Row Sums". In our example, that would be a total of all the months, appearing on the far right side of the results. Most Click "Next" when done.

6) The next window will ask for the name of the query. Enter an appropriate name, such as "qrySalesReportCrossTab". Make sure that the option button for "View The Query" is checked, then click the "Finish" button.

7) Does the data look correct? If not, you may need to do some "tweaking". For instance, you may be getting a column for each and every date from the sales table. What you really want is to separate these by month. To do this, simply switch into design view, and click on the "Field" row for the item in question (that should be the one with "Column Heading" in the "Crosstab" row). In our example, that is the sales date, which contains the date. Since we want to pull out the month, we can use the month function. Click on the field name, then insert this code before the field name: SalesMonth: Month([Sales_date])
The month function will pull the month out of the date field. Obviously, if your table contains multiple fiscal years, you will want to use the Year function to ensure that you are getting the correct data.

8) The crosstab query you just created now becomes the recordsource for your new report.

Feedback, comments?
Randy Smith, MCP
rsmith@cta.org