CSE 2111: Inner Joins, Outer Joins

Inner Join is the

default join type in Access

Inner Join: The Dynaset only includes records that have

matching keys on both tables.

An outer join dynaset will

display all records from one table, and only matching records from the other table.

Access: Join Properties: 1

Inner join.

Access: Join Properties: 2

Outer Join from Left (usuallyPrimary Key Table)

Access: Join Properties: 3

Outer Join from Right (usually Foreign Key Table)

Access: Join Properties: Typically choose

2

An Arrow represents

an Outer Join

Many-One-Many

NOT VALID/Invalid

One-Many-One

OKAY/Valid

[Brackets] around

• Table/Query
• Exclamation Point ! after

Blank Value =

NULL value

Need NZ Function for

EACH field

NZ Syntax

Nz(variant [,valueifnull])

If the variant argument does NOT evaluate to NULL,

the Nz() function will return whatever the variant argument evaluates to.

If the variant argument does evaluate to NULL and if the valueifnull argument is populated (not defaulted),

it will return the valueifnull value.

If the variant argumentevaluates to NULL and the valueifnull argument is NOT populated, then it will

default to an empty text string when used in an expression in a query.