By Colin Wilcox

Do you create reports from data stored in Excel worksheets or in text files? If so, you can save time and create attractive reports by using two features in Access: linked tables and the Report Wizard.

Microsoft Access provides some powerful (not to mention easy-to-use) reporting features. One of those features is the Report Wizard, which lets you create attractive reports from data stored in a variety of sources, including Microsoft Excel worksheets and text files. Another such feature is the ability to create linked tables, which provide a way to synchronize your reports with your source data. When you create a report by using linked tables, the data in the report changes automatically whenever the source data changes.

You can use the techniques described here to create nearly any kind of report. The following steps use data from the Northwind Traders sample database supplied with Access. Northwind Traders is a fictitious grocery wholesaler, and you can use the data to create and change a product report.

If you're new to Access, don't be shy about trying to do this. Who said becoming a power user had to be hard?

Getting started

To get started, download this sample spreadsheet to your C:\Program Files\Microsoft Office\Office 10\Samples folder. In the following steps, you use Access to create a table that links to the worksheet, and then you create a report from the table. From there, you change some of the data on the worksheet and see how easy it is to update the report automatically.

Create the linked table and the report

First you create a table that links to the sample worksheet, and then you use the Report Wizard to create the report. These steps recreate the Alphabetical List of Products report from the Northwind sample database in Access.

To create the linked table

1. Start Access. On the Help menu, point to Sample databases and then click Northwind Sample Database. If the Northwind Traders welcome screen appears, click OK to dismiss it. Close the Main Switchboard, too.
2. On the File menu, point to Get External Data, and then click Link Tables.
3. From the Files of Type list, select Microsoft Excel (*.xls).
4. Navigate to the folder where you saved the sample worksheet (C:\Program Files\Microsoft Office\Office 10\Samples), click SampleSourceData.xls, and then click Link.
5. In the Link Spreadsheet Wizard, ensure that Show Worksheets is selected, and then click Next.
6. Ensure that First Row Contains Column Headings is selected, and then click Next.
7. In the Linked Table Name box, type Products Linked, click Finish, and then click OK to close the alert message.

The linked table appears in the Database window as an Excel icon, and Access marks it with an arrow to show that it is a linked table:

To create the report

1. In the Database window, click Reports, and then double-click Create report by using wizard.
2. In the Report Wizard, go to the Tables/Queries list, and then select Table: Products Linked.
3. Click >> to move all of the available fields from the Available Fields list to the Selected Fields list, and then click Next.
4. To accept the default grouping levels, click Next.
5. Select Product Name from the first drop-down list, and then click Next.
6. Select the Columnar layout option, ensure that the Adjust the field width so all fields fit on a page option is selected, and then click Next.
7. Select the Soft Gray style, and then click Next.
8. In the What title do you want for your report box, type Alphabetical List of Products Linked, and then click Finish.

Access creates and opens the report. Now you have an attractive visual display that tells you key information such as units in stock, unit price, and so on. Reading this is much easier than scanning a plain Excel worksheet!

Alter the data and update the report

Let's update the source data and the report with some small changes. To follow these steps, you must close the Access report. Close any reports that use data from your linked tables. If you don't, Excel will not allow you to open the worksheet that contains the source data.

To alter the data

1. Start Excel and open SampleSourceData.xls, the worksheet you downloaded at the beginning of this article.
2. Locate the Aniseed Syrup entry on row 4, and change the value in the Quantity Per Unit column to 24-550 ml bottles.
3. Locate the Alice Mutton entry on row 18, and change the value in the Quantity Per Unit column to 35-3 KG tins.
4. Save the worksheet, go back to Access, and open the Alphabetical List of Products Linked report.

You can see that the first two entries use the new values from the worksheet.

Keep sending that e-mail!

We look forward to receiving your e-mail messages at pwruser@microsoft.com. We really want this to be your column, so please send us your comments and favorite handcrafted Office solutions. Remember, we will not be able to feature every Office solution that we receive, we will not have the time to respond to all of your e-mail, and we are not technical support representatives. But we may feature your solution in an upcoming column.

About the author

Colin Wilcox writes for the Office Help team. In addition to contributing to the Office Power User Corner column, he writes articles and tutorials for Microsoft Data Analyzer.