Creating a Data Source Query

Although you can switch to PivotTable or PivotChart view from a form, table, or query, in most cases this won’t produce good results. A useful PivotObject is generally based on a query combining data from 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. Rarely, a single table might contain all the data needed for a PivotObject—generally, that would be a flat-file table imported from a mainframe database, or perhaps a table created from data in other tables using a make-table query.

When creating a query to use as a data source for PivotObjects, include all the data you might want to analyze. In the case of date and time data in particular, it is often useful to write some column expressions to preprocess the data to create appropriate column or row headings. Typically, you will need to include several linked tables in the query, far more than you would use for a form or report—possibly all the data tables in the database. As an example (possibly carried to a ridiculous extreme), I made a query called qryNorthwindEverything that includes all the tables in the Northwind sample database—or actually, copies of these tables renamed with appropriate LNC tags, in the sample PivotTables and PivotCharts database. This query is shown in design view in Figure 5.1.

Figure 5.1

The calculated expressions for this query are explained below.

Concatenate the salesperson’s first and last name:

Salesperson: [FirstName] & “ “ & [LastName]

Extract the year from the order date:

OrderYear: Year([OrderDate])

Extract the month number from the order date, and get the month name from it:

OrderMonth: MonthName(Month([OrderDate]))

Create an expression with the letter “Q,” the quarter number, and the year from the order date:

OrderQuarter: “Q” & DatePart(“q”,[OrderDate]) & “, “ & Year([OrderDate])

Customer name (CompanyName in tblCustomers):

Customer: CompanyName

Price, calculated as UnitPrice * Quantity * Discount (if any):

Price: CCur(tblOrderDetails.[UnitPrice]*tblOrderDetails.Quantity*IIf([Discount]>0, [Discount],1))

Supplier name (CompanyName in tblSuppliers):

Supplier: CompanyName

Shipper name (CompanyName in tblShippers):

Shipper: CompanyName

With a query like this, you can make PivotObjects that analyze just about anything you want in the Northwind database.