Access Intermediate Training Course

Build Reliable Relationships

Creating Relational Databases

Create Related Tables

- Create separate tables for each entity (e.g., Customers, Orders).

- Use AutoNumber for primary keys and Number for foreign keys.

Add Referential Integrity

- Go to Database Tools → Relationships.

- Drag related fields between tables → Check Enforce Referential Integrity → Click Create.

Working with

Related Tables

1. Use the Lookup Wizard

- In Table Design View, select a field → Set Data Type to Lookup Wizard.

- Choose I want the lookup field to get values from another table → Select the source table and field.

2. Change Lookup Fields

- Open table in Design View → Select the lookup field → Modify Row Source or Bound Column in the Lookup tab.

3. Input Data into Related Tables

- Use Forms via the ‘Create’ tab with subforms or combo boxes to simplify data entry.

- Ensure foreign key fields use lookup fields for dropdown selection

Control Data Entry

Defining Data Entry Rules

1. Use Input Masks

- In Design View → Select a field (e.g., Phone Number) → Set Input Mask (e.g., (999) 000-0000).

2. Set Field Properties

- Use the General tab in Design View to set Field Size, Format, Default Value, etc

3. Set Validation Rules

- In Design View → Select a field → Set Validation Rule (e.g., >=0) and Validation Text (e.g., “Enter a positive number”).

Query with Power

Using Advanced Query Features

1. Join Tables in Queries

- Create a new query → Add tables → Drag related fields to create joins.

- Use Inner Join (default) or right-click to change to Left/Right Join.

 2. Create a Calculated Field

- In Query Design → Add a new column: TotalPrice: [Quantity]*[UnitPrice].

 3. Use Action Queries

- Types: Make Table, Append, Update, Delete.

- Create a query → On the Design tab, choose the action type → Define criteria → Run.

Creating Advanced Queries

1. Summarize and Group Values

- In Query Design → Click Totals (Σ) → Group by desired field → Use aggregate functions (Sum, Avg, Count).

2. Create a Crosstab Query

- Use the Crosstab Query Wizard → Select row headings, column headings, and value field with an aggregate function.

3. Use Parameter Queries

- In Criteria row, enter a prompt in square brackets (e.g., [Enter Start Date]).

Design Better Outputs

Creating Advanced Form Design

1. Work with Graphics

- In Form Design View → Use Image control → Insert pictures (e.g., logos).

2. Add Calculations

- Use Text Box control → Property Sheet → Set Control Source to an expression (e.g., =[Quantity]*[UnitPrice]).

3. Use Combo Boxes

- Add a Combo Box → Use wizard to pull values from a table/query or type manually.

 4. Use Unbound Controls

- Add a Text Box or Button not linked to data → Use for navigation, display messages, or calculations.

Using Advanced Report Features

 1. Customize Headers and Footers

- In Report Design View → Use Design tab to add Report Header/Footer or Page Header/Footer.

2. Add Calculated Values

- Insert a Text Box → Set Control Source to an expression (e.g., =Sum([TotalPrice])).

3. Work with Sub-Reports

- Use Subform/Subreport control → Link to related data → Set Link Master/Child Fields.