Accessing and Analyzing Data with Microsoft Excel (Bpg-Other)

Some of the data analysis you’ll conduct in Access might require you to bring external data into Access first. Access provides two ways to retrieve external data: importing and linking. Data that you import is copied from the original data source, and changes you make to the imported data in Access are not reflected in the original data source. Linked data remains in the original data source, and changes you make to the linked data in Access are carried through to the original data.

You should import data when

• You want to store and manage the data within Access.

• Network connectivity is limited or does not exist. (Linked data requires a live data connection, usually to a data source on another computer.)

• You do not often change the original data.

• You want to take “snapshots” of the original data for data analysis purposes only.

You should link to data when

• You want to use the Access data analysis features and tools, but you do not want to store or cannot store the original data in Access (for example, the original data is several gigabytes in size).

• You need more robust database management features, such as greater disaster recovery of your data, which typically means your data is stored on a server.

• You have dedicated and abundant network connectivity; for example a desktop computer connected directly to a high-bandwidth corporate intranet.

• Dozens or perhaps hundreds of users are constantly changing the data at the same time.

• You want to see everyone’s changes to the original data frequently or in real time.

Importing Data

To import external data into the active database, point to Get External Data on the File menu and then click Import. In the Files Of Type list, select a file type. Acceptable file types include the following:

• Another Access database

• dBase

• Excel

• Microsoft Exchange or Microsoft Outlook folder or address book

• Hypertext Markup Language (HTML)

• Lotus 1-2-3

• Paradox

• Text

• Extensible Markup Language (XML); not available in Access 2000

• Any other data source for which you have an Open Database Connectivity (ODBC) driver.

Choose a file of the file type you selected, and then click Import, or follow the directions Access displays on your screen to import the data from the data source. For example, if you import an Excel workbook, Access asks you to select the worksheet in the workbook that contains the data you want to import.

Importing Data from Excel

Because of the similarity of the row-and-column structure between Excel worksheets and Access data tables, Access is well equipped to import Excel data into a new Access data table or append Excel data to an existing Access data table.

When you import Excel data, the Access Import Spreadsheet Wizard can detect column headings and use those column headings as field names. It also allows you to select named cell groups or cell groups on different worksheets in a workbook.

If the structure of the Excel data is not the same as the Access data table into which you’re importing data, you might run into import errors. To minimize these errors, do the following:

• Excel data should be available as lists of data records, and lists should start in cell A1 of an Excel worksheet. Also, only one data list should be in the worksheet.

• Each column of the Excel data must have the same data type (for example, text, a date, or currency) as the corresponding field in the destination table, and the fields must be in the same order (unless you’re using the first row of the data list as field names, in which case the field names must match).

• Ensure that data records you’re importing do not contain duplicate values for any corresponding primary key defined in the destination table.

• Make sure that each worksheet has the same number of columns as fields in the destination data table.

Your Turn

In this exercise, you will import data from an Excel workbook and then use Access to create a data entry form and a report based on the imported data.

1. Start Access, and open the Northwind.mdb file in the Chap05 folder. (If the Welcome screen appears, click OK to close it. Also, if the Main Switchboard form appears, click the Display Database Window button to display the Database window.)

2. On the File menu, point to Get External Data and then click Import.

3. In the Files Of Type list, select Microsoft Excel.

4. Locate and select the CustServ.xls file in the Chap02 folder, and then click Import.

5. Click Next, select the First Row Contains Column Headings check box, and then click Next.

6. Click Next two more times, and then click the No Primary Key option.

7. Click Finish, click OK, and compare your results to Figure 5-3. Notice that the data from the CustServ.xls file’s Original Data worksheet is imported and becomes a data table in Access.

   Figure 5-3: Results of importing the CustServ.xls file’s Original Data worksheet.

Now create a data entry form for entering new data into the data table.

1. On the Insert menu, click Form.

2. Click Form Wizard, and then click OK.

3. Move all of the items in the Available Fields list to the Selected Fields list, and then click Finish. A data entry form is created and displayed.

Finally, create a report that summarizes some of the data in the data table.

1. Close the form, and then click Report on the Insert menu.

2. Select Report Wizard. In the Choose The Table Or Query Where the Object’s Data Comes From list, select Original Data, and then click OK.

3. Move the Year, Month, and Cleanliness items from the Available Fields list to the Selected Fields list, and then click Next.

4. In the Do You Want To Add Any Grouping Levels List, click Year, click the right arrow (>), and then click Next.

5. Click the Summary Options button, select the Avg check box, click the Detail And Summary Only option, click OK, and then click Finish.

You can see the yearly summarizations and averages in the report.

Linking to External Data

To create a table in the active database that’s linked to a table in an external database, point to Get External Data on the File menu and then click Link Tables. The Link dialog box appears. In the Files Of Type list, select a file type. Acceptable file types include the following:

• Another Access database

• dBase

• Excel

• Microsoft Exchange or Microsoft Outlook folder or address book

• Hypertext Markup Language (HTML)

• Paradox

• Text

• Any other data source that supports linked tables and for which you have an Open Database Connectivity (ODBC) driver.

Choose a file of the file type you selected, and then click Link, or follow the directions Access provides to link to the data source. These directions will be similar to those for linking to an Excel workbook.

Your Turn

In this exercise, you will link to a table in another Access database file. To show that the data is linked, you will make changes to the data in the external data table and see the changes reflected in the linked data table.

1. If the Northwind.mdb file is not already open, start Access and open the Northwind.mdb file in the Chap05 folder. (Close the Welcome screen if it appears; if the Main Switchboard form appears, click the Display Database Window button to display the Database window.)

2. On the File menu, point to Get External Data and then click Link Tables.

3. Locate and select the Relation.mdb file in the Chap02 folder, and then click Link.

4. On the Tables tab, select Nonrelational Data in the list of tables and then click OK. Compare your results to Figure 5-4. The Nonrelational Data table appears in the list of tables in the Northwind database and is marked with an arrow icon to indicate that the table is linked.

   Figure 5-4: Results of linking to the Relation.mdb file’s Nonrelational Data table.

5. Open the linked Nonrelational Data table and notice the address for receipt number 1 (123 Main St.)

6. Open the Relation.mdb database in the Chap02 folder, and then open the Nonrelational Data table.

7. Change the address for receipt number 1 to 789 Central Court.

8. Open the Northwind.mdb database in the Chap05 folder, open the linked Nonrelatonal Data table, and notice that the address for receipt number 1 has changed to 789 Central Court.