Tables & Queries:

Title: Jan's Illustrated Computer Literacy 101
Did you want: Working with Databases: Access 2007, 2010, 2013, 2016

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 Symbol: Infinity 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.

Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro Arrow: subtopic open

Project 2: Access Basics

Project 3: Tables & Queries Arrow: subtopic open
    Designing TablesTo subtopics
    Designing QueriesTo subtopics
    ExercisesTo subtopics

Project 4: Forms & Reports


What You Printed for Project 3: Tables & Queries



What is it?

# of pages

1. Document a Table Report: Relationships
Datasheet: Projects
Documenter Report: Project table
(possibly- Documenter Report: Staff table)
2. Document a Query Documenter Report: QStaff-FullName
Icon: Access 2003 Print Screen: objdep-QStaff-FullName.gif

Skills Covered

Skill Lesson
Designing Tables
  • Think through the steps to design and redesign of a table
  • Change field size
  • Rename a table
  • Copy and paste data between tables
  • Use a foreign key
  • Create a relationship between tables
  • View subdatasheets
Design Tables
  • Create a table with Table Wizard
  • Set the Format property for a field
  • Enter data using an input mask
  • Edit data in a field with an Input Mask
  • Create a custom Input Mask for a field
Format Fields
  • View a tables indexes
  • Create an index using several fields
  • Add an index
  • Set the Validation Rule and the Validation Text properties
  • Understand warning messages about breaking the rules
  • Set a Validation Rule and Validation Text for the table
  • Test the effect of validation rules
  • Add records after rules were created
  • Delete a field from datasheet view
  • View subdatasheets
  • Add a table to the Relationships window
  • Change field to a Lookup field
  • Select values using a Lookup field
  • Delete a field from datasheet view
  • Create a relationship between tables
  • View subdatasheets
Redesign Table
  • Print Relationships
  • Print a table datasheet
  • Document a table with Documenter
  • View object dependencies
Document a Table
Designing Queries
  • Design a select query manually
  • Add all fields from a table at once to grid
  • Clear the design grid
  • Add fields to design grid
  • Move columns in a query
  • Use criteria in a select query
  • Combine criteria in a select query
  • Find Null or Not Null values
  • Use Like operator
  • Name and save a query
Select Query
  • Copy and rename a query
  • Create a new field by concatenating text fields
  • Use an IIf statement to define a new field
  • Use a query as source for a Lookup field
  • Change Lookup properties to match changed query
  • View object dependencies
Calculated Values-Text
  • Add currency field
  • Enter character not on keyboard
  • Create a calculated number field
  • Format a calculated value
  • Calculate a value with IIf
  • Use nested IIf statements
Calculated Values-Numbers
  • Use the Totals row to count records
  • Open a second database while the first remains open
  • Use the Totals row to sum and to average
  • Group a total's results on a calculated field
Calculated Values-Totals
  • Write a simple calculation for a new field
  • Use the Format function
  • Use the Expression Builder
  • Get Help for a function from Expression Builder
  • Use the DateDiff function
  • Correct a date calculation based on the current date/time
  • Use the DateAdd function
  • Group and count records
  • Use the DatePart function
Calculated Values-Dates
  • Create a parameter query
Parameter Query
  • Create a make-table query
Make-Table Query
  • Create an append query
Append Query
  • Create a delete query
Delete Query
  • Create an update query
Update Query
  • Create a crosstab query with the Crosstab Query Wizard
Crosstab Query
  • Use Documenter to document a query
  • View object dependencies for a query
  • Capture an image of the window
  • Add text to an image
  • Print a screen capture
  • Compact and repair a database
Document a Query

Important Terms

Action query
aggregate function
Append query
Avg function
calculated field
Caption property
cascade delete
cascade update
Character Map
Count function
Crosstab query
DateAdd function
DateDiff function
DatePart function

default value
Delete query
Expression Builder
field size
foreign key
Format function
Format Property
Group By
IIf function
Input Mask Property
Like operator

Make-Table query
Max function
Min function
Object Dependencies
Parameter query
primary key
propagating nulls
referential integrity

screen capture/screenshot
square brackets
Sum function
Update query
Validation Rule
Validation Text
wildcard character
zero-length string
Zoom window