Part 1: Excel

Getting Started

• Rules for Database Set-up
• Access Database Setup
• Rules for Excel Lists

Error Messages

• Existing Access table
• New Access table

Correcting Import Errors in Excel

• Apostrophes and literal characters
• Excel defined input masks
• Deleting Access error table
• Re-import clean Excel data

Preparing the Excel Spreadsheet

• Converting Text to Columns
• Using Find and Replace
• Creating a Unique Identifier/Primary Key

Importing Excel Data into Access

• Access “Find Duplicates” Query Wizard
• Correcting Duplicate Fields in Excel

Excel Ranges

• Working with Named Ranges
• Creating Named Ranges in Excel
• Importing Excel Named Ranges into Access

Excel Lists

• Linking with Lists
• Creating Lists in Excel
• Linking Excel Files to an Access Database

Part 2: Access

Separating Fields in Access

• Creating Unique Identifier/Primary Key in Access

Working with Access Make-Table Queries

• Relating and Adding Fields in a Query
• Creating Make-Table Queries
• Checking for Duplicates

Modifying Data Errors in Access

• Working with the NOT Operator in a Query

Working with Update Queries

Working with Append Queries

• Using SQL
• Creating SQL and an Append Query to Extract Unique Data
• Creating an Append Query and Mapping Fields

Modifying Leading Zeros in Access

Working with Access Reports

• Report Queries
• Creating Access Reports

Sending Access Data back to Excel

• Sending an Access Query to Excel
• Sending an Access Table to Excel
• Sending an Access Crosstab Query to Excel