Tables & Queries:
Summary

Tables must be designed carefully in a relational database. Good design means avoiding duplications and reducing the possibilities for errors. Related tables share a field or set of fields that act as the primary key in one table and a foreign key in the other table.

Each table has a number of properties. Each field in that table has its own properties. Validation rules for a table apply to whole records and can compare field values with other field values. A validation rule for a field cannot compare the value to another field's value.

The Format and Input Mask properties for a field apply at different points in the process of creating or editing a field. This can be quite confusing. The Format Property is applied after the field's value is saved, usually by leaving the field. The Input Mask property applies while you are entering or editing values in the field.

Indexes help Access search and sort more quickly. Too many indexes can, however, slow down these features.

Subdatasheets are available when tables have a One-to-One or a One-to-Many relationship. In the table on the One side, you can open a subdatasheet to see the related records on the Many side.

Relationships can require referential integrity to be enforced. This requires a table on the Many side of a relationship to link to an existing record only. Cascading deletions and updates may or may not be allowed. In the Relationships window a One-to-Many relationship shows 1 at one end of the join line and at the other end.

A Select query can sort records and pick out certain records by using criteria. You can calculate a new field using the existing values (text or number) and even include fixed text. When you run a Parameter query, you are asked for input that will be part of the criteria. This makes for more flexible queries.

Access includes a number of functions that you can use to calculate new values, especially for calculations involving dates and times. The IIf function creates a conditional expression that allows you to produce one value when an expression is true and a different value if the expression is false.

A Totals query can use one of several aggregate functions to do a calculation involving all of the values in a field, such as Sum or Average.

Action queries not only select records but they then DO something with those records. Such actions are not in the Undo list! Action queries can make a new table, add records to an existing table, delete records, or update values.

A Crosstab query creates some kind of Total where the datasheet has one or more fields with their values down the left and one field with its values across the top. The Crosstab Query Wizard is recommended when creating this rather complicated type of query.

Documenting your table and query designs is very helpful to others who work with your database later, and is also helpful to you after the details have faded as to why you choose as you did. You can print table databases but you cannot print the table design. The Documenter feature produces a detailed list of all the properties for your table or query.

In Access 2003 the Object Dependencies task pane shows a tree of dependencies. You can use this information to avoid changing or deleting objects in a way that breaks other objects.

From time to time it is helpful to compact and repair the database. This reduces its size on the disk and also allows Access to correct internal errors before they cause obvious problems.

What You Printed for Project 3: Tables & Queries

	Lesson	What is it?	# of pages
1.	Document a Table	Report: Relationships Datasheet: Projects Documenter Report: Project table (possibly- Documenter Report: Staff table)	1 1 3 (10)
2.	Document a Query	Documenter Report: QStaff-FullName Print Screen: objdep-QStaff-FullName.gif	4 1

Skills Covered

Important Terms

~~  1 Cor. 10:31 ...whatever you do, do it all for the glory of God.  ~~

Last updated: 30 Apr 2012