MS Access Using Advanced Query Features Instructor Vicki

MS Access: Using Advanced Query Features Instructor: Vicki Weidler Assistant: Joaquin Obieta

Overview • • • Understand the differences between outer, inner, and self-join queries Design outer, inner, and self-join queries Generate calculated fields in a query Modify the format of a displayed value Use the expression builder Create queries to add, delete, and modify data in a table and to produce new tables

Joins Queries that include data from more than one table are called joins. A join tells MS Access how the data between tables is related. You can create: • Outer joins • Inner joins • Self-joins

Outer Joins An outer join is a query in which all of the records from one table are joined to only matching records from another table.

Inner Joins If one table does not have a matching record in the second table, then no records from either table will appear in the query results. To avoid this, you can use an intermediate table to extract data from the original two tables. This intermediate, or join table, acts as a bridge between two tables that don’t have any related fields. This is used when a manyto-many relationship exists between two tables and uses primary keys from both tables to extract the data from the intermediate join table.

Self-Joins A self-join is a query that displays matching records from the same table when there are matching values in two fields.

The Expression Builder

Action Queries Action queries perform certain actions in tables. There are four types: • Append-append data from one table to another • Delete-delete records from tables based on specific criterion • Update-update data in different tables at the same time • Make-Table-creates a table from the result of a query

Summary • • • Understand the differences between outer, inner, and self-join queries Design outer, inner, and self-join queries Generate calculated fields in a query Modify the format of a displayed value Use the expression builder Create queries to add, delete, and modify data in a table and to produce new tables

Conclusion • Resources • Questions & Answers • Evaluations • Thank You!!!