Crosstab Queries


Crosstab queries are used to create cross-tabulated totals, counts, or other aggregate functions—that is, when you want a total for all the records matching each value in a field. A totals query can give you a total for all the sales made by all salespersons, but if you want to see separate sales totals for each salesperson, you need a crosstab query.

If you have Access 2002 or higher, I recommend using a PivotTable instead of a crosstab query, because they are easier to use, more attractive, and more flexible than crosstab queries. See Chapter 5, Using PivotTables and PivotCharts to Interact with Data, for more information on PivotTables.

Access provides a handy Crosstab Query Wizard that you can use to create a crosstab query. However, before you run the wizard, it’s advisable to do some advance work by preparing a query that includes all the data you need. A crosstab query needs a minimum of three fields: one for the row headings, one for the column headings, and one for the data that is used for the aggregate function. Unless your data is in a flat-file table imported from a text file or a mainframe database, you’ll probably need to construct a data source query based on a several tables, giving you a selection of fields useful for row and column headings, and other fields with data that can be counted, summed, or otherwise analyzed for the data area. It is often useful to create calculated fields to preprocess the data or alias fields to create appropriate column or row headings. This all-inclusive query can then be used as the data source of any number of crosstab queries, which saves considerable effort compared to creating each crosstab query from a combination of tables.

I made a query based on most of the data tables in the sample Toy Workshop database, for use as a data source for crosstab queries. This query (shown in Figure 4.14) has a number of aliased fields and a calculated TotalPrice field.

click to expand
Figure 4.14

To ensure that your crosstab query’s field names are what you want them to be, set the Caption property of aliased fields in the field properties sheet, as shown in Figure 4.15 for the aliased CompanyName field. Otherwise, the field may display with the original table field name, not the alias you created in the query column.

click to expand
Figure 4.15

If you use a concatenated name field (last name first) or a company name for row or column headings in a crosstab query, you will see underscores replacing the final periods in names like Toy Specialities, Ltd. or Davolio, Nancy H. If you don’t like the way this looks, you can use an expression like the following in the data source query to remove the trailing periods from the customer names, and a similar expression for the salesperson name:

Customer: IIf(Right([CompanyName],1)=”.”,Left([CompanyName],Len([CompanyName])-1),[CompanyName])

Once the data source query has been created, you can use the Crosstab Query Wizard to create the crosstab query—or just create a new query, drag qryOrders to its top pane, switch the query type to Crosstab Query in the Query Type selector, and select the fields for the crosstab query manually. To use the wizard, follow these steps:

  1. Click the New button in the datasheet window, select Crosstab Query Wizard in the New Query dialog, and click OK.

  2. On the first screen of the wizard, select qryOrders as the data source, as shown in Figure 4.16. Click Next to go to the next screen of the wizard.

    click to expand
    Figure 4.16

  3. On the next screen, select the field or fields for use as row headings; I selected ToyCategory, as shown in Figure 4.17. Click Next to go to the next screen.

    click to expand
    Figure 4.17

  4. On the next screen, select a field for use as column headings, and click Next. I selected Salesperson, as shown in Figure 4.18.

    click to expand
    Figure 4.18

  5. On the next screen, select the field with the values to be calculated and the function to use for the calculations, then click Next. I selected the TotalPrice calculated field and the Sum function, as shown in Figure 4.19.

    click to expand
    Figure 4.19

  6. On the final screen, give the crosstab query a name; I use the tag qxtb for crosstab queries. I named the query qxtbToyCategoriesBySalesperson, to indicate how it tabulates the data.

Figure 4.20 shows the new crosstab query in Design view, and Figure 4.21 shows the same query in Datasheet view.

click to expand
Figure 4.20

click to expand
Figure 4.21

You can use the same data source query to create many different crosstab queries, selecting different fields for row and column headings, or using different functions to analyze the data.




Expert One-on-One(c) Microsoft Access Application Development
Expert One-on-One Microsoft Access Application Development
ISBN: 0764559044
EAN: 2147483647
Year: 2006
Pages: 124
Authors: Helen Feddema

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net