Some ACCESS reminders

For the database TABLES

The Relationship Diagram is drawn for the database i.e. separate from the query.

A relationship between two tables must follow 3 rules:

One of the two fields is a primary key

The two fields have the same type

The two fields have the same meaning

FYI: The two fields do NOT have to have the same name.

A table might or might not have a primary key.

A table might or might not have a foreign key.

A table can have multiple foreign keys.

A table can have both a primary key and foreign key(s).

A table MUST have at least one kind of key.

In the real world, a table could have multiple primary key fields, but we won’t ;o)

Data Integrity exists when all the foreign key values exist in the primary key field.

Referential Data Integrity is defined PER RELATIONSHIP in the relationship diagram. You can only cascade update and/or cascade delete if data integrity is enforced.

For QUERIES

The Join type is chosen on a query per table-to-table relationship i.e. per line that connects two tables together.

Access only and always “sees” ONE TABLE per query, no matter how many you choose to designate.

If you have only one table on your query, then there is no JOIN TYPE i.e. leave it blank.

Inner and Outer joins relate to the window that pops up when you right click on the relationship that you have between two tables in a query.

Only if you are using an OUTER join do you fill in the RELATIVE TO part of the query grid i.e. inner joins do not use this information. This information is a table name, NOT a field name.

An inner join and an outer join relative to the foreign key table are the same IF there are no data integrity errors.