Chapter 33: Getting Data from External Database Files
681
On the CD
The database file used in this example is available on the companion CD-ROM. It’s an Access 2010 file named
budget data.accdb.
The database file
The database file used in this example is a single-table Access file that consists of 31,680 records.
This table contains the following fields:l (^) Sort: A numeric field that holds record sequence numbers.
l Division: A text field that specifies the company division (Asia, Europe, N. America,
Pacific Rim, or S. America).
l Department: A text field that specifies the department within the division. Each division
is organized into the following departments: Accounting, Advertising, Data Processing,
Human Resources, Operations, Public Relations, R&D, Sales, Security, Shipping, and
Training.
l Category: A text field that specifies the budget category. The four categories are
Compensation, Equipment, Facility, and Supplies & Services.
l Item: A text field that specifies the budget item. Each budget category has different budget
items. For example, the Compensation category includes the following items: Benefits,
Bonuses, Commissions, Conferences, Entertainment, Payroll Taxes, Salaries, and Training.
l (^) Month: A text field that specifies the month (abbreviated as Jan, Feb, and so on).
l Year: A numeric field that stores the year (either 2008 or 2009).
l (^) Budget: A numeric field that stores the budgeted amount.
l Actual: A numeric field that stores the actual amount spent.
l (^) Variance: A numeric field that stores the difference between the Budget and Actual.
The task
The objective of this exercise is to create a report that shows the first quarter 2009 (January
through March) Compensation expenditures of the Training Department in the North American
Division. In other words, the query will extract records that meet all the following criteria:l (^) Division: N. America
l Department: Training
l (^) Category: Compensation
l Month: Jan, Feb, or Mar
l (^) Year: 2009