Microsoft Office Tutorials and References
In Depth Information
Using Excel Data in Access
Using Excel Data in Access
About a year ago, Caitlin realized that her employees were reporting more and more
instances of clients requesting additional services. Her employees also indicated that the
clients, or the clients’ families, often were willing to pay for the additional services. Caitlin
tracked this information in an Excel workbook. She noted the services requested, the num-
ber of clients who requested the service, and the average amount the clients were willing
to pay. She based her new business plan, in part, on the information in this worksheet.
Caitlin wants to add a table to page 3 of the brochure showing an example of a typical
service package based on the services requested most often by clients. To determine which
services are the most frequently requested, you will analyze the data in Caitlin’s workbook.
Although Excel is perfect for calculating totals and creating charts to track trends, Access is
a better choice to use to extract information, such as which services are the most frequently
requested. To find that information, you will import the Excel data into Access.
To create a table showing the most frequently requested services, you’ll first import the
list of requested services from the Excel worksheet into an Access database. Then you will
perform a query in Access to determine which services were requested the most often.
You will then export the results of the query to Word and insert them into the brochure.
Preparing an Excel List for Import
You can use lists of Excel data to build tables in Access. Then you can create forms,
reports, queries, and other Access objects based on the tables. You can only import Excel
data that is in the form of a list—a series of worksheet rows that contain related data, such
as product names and prices or client names and phone numbers. Caitlin’s worksheet
showing the service requests is in the form of a list.
Before you import the Excel list, you should check the format of the data. The import
will be seamless and error free if each column in the Excel worksheet has a label in the
first row and the list of data contains no blank rows or columns. Caitlin asks you to pre-
pare the worksheet before you import its data to Access.
To prepare the Excel data:
1. Open the Excel workbook LHCRequests , located in the Tutorial.02\Tutorial folder included
with your Data Files. This is the Excel workbook that contains the information about the ser-
2. Save the file as Request List in the Tutorial.02\Tutorial folder included with your Data Files.
The first eight rows contain helpful information for someone reading the worksheet, but the
first row of data needs to contain the column headings.
3. Select rows 1 through 8 , right-click the selection, click Delete on the shortcut menu to
delete the selected rows, and then click the worksheet to deselect the cells. Excel deletes
the rows and shifts the remaining data up eight rows.
The first column does not have a column head. The column headings will become the field
names in the table, so it is important that every column have a heading. You will add a
heading to the first column.
4. Click cell A1 , type Service , and then press the Enter key.
It’s a good idea for field names in Access not to contain any spaces, so you’ll change the
column headings now.