Title: "Excel vs. Access" the Epic Battle Continues

Presenter: Lisa Friedrichsen

New Terminology

Data Scrubbing

Data Cleansing

Data Stewards

Data Quality Analysists

Data Scientists

to prepare an Excel spreadsheet for import into Access
1.Row 1 = field names

  • No spaces
  • No odd characters
  • No reserved words (Date, Name, Page, Field, If, so forth…)
  • 1 row and 1 row only
  • Tricky! No leading spaces!

2.Rows 2 and beyond = records

No blank rows -- Unhide Columns

No blank columns -- Unhide Rows

Blank cells are AOK

No calculated rows

No calculated columns

3.Clean the fields

Sorting both ascending and descending on each field name can "reveal" the errors

4.Clean the margins

Delete a few columns on the right edge

Delete a few rows at the bottom

To import the data into Access….

  1. In Access, click the External Database tab on the Ribbon.
  2. Click the Excel button in the Import & Link group.
  3. Follow the steps of the Get External Data - Excel Spreadsheet Wizard.

To create a lookup table….

  1. Create a query from the (child/many table) with the field(s) you want in the lookup (parent/one) table.
  2. Use the Totals button to summarize the redundant data into one record per value.
  3. Use the Make Table button to create the Lookup table with unique values.

To relate the lookup table (parent/one) to the child/many table….

  1. Use the Lookup Wizard starting in the foreign key field. (The linking field on the child or "many" table.)

Two benefits to building relationships this way.…

  1. Creates the relationship.
  2. Applies Lookup properties.

(To check the Relationships, go to the Relationships window and click the All Relationships button on the Design tab.)

To find orphans….

1.Create a query in Design View.

2.Add the "one" and "many" tables.

3.Add the linking field from both the "one" and "many" tables (and any other fields needed to clarify the records).

4.Double-click the link line and choose the option which creates a RIGHT join (selects ALL records from the "many" table.

5.Add Is Null criteria to the field from the "one" or parent table.

6.Run the query / View the datasheet.

7.You've just isolated your orphan records. Often, they are garbage and should just be deleted. Sometimes they can be fixed. That process involves analysis of each situation.

8.Once all of the orphans are dealt with, you can enforce referential integrity on the relationship in the Relationships window.

Referential Integrity on a relationship means that orphans cannot be created….

  1. You cannot delete a record in the "one" or parent table that has related records in the "many" or child table.
  2. You cannot create a record in the "many" table that doesn't have an existing match in the "one" table.

…with one important exception!

Access allows NULL values in the foreign key field. Set the Required property to TRUE for all foreign key fields to prevent this!

So you might also want to create a query on the "many" table and search for Is Null in the foreign key field!

Tips on working with Null values: