Jan's Illustrated Computer Literacy 101 Logo:Jegsworks Jan's Illustrated Computer Literacy 101
Home > Jan's CompLit 101 > Working with Databases > Tables & Queries > Summary
Icon: Arrow - Previous pagePrevious    NextIcon: Arrow - Next page

Jan's Working with Databases

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 Symbol: Infinity at the other end.

You can import data in a number of different formats or even objects from another database. You can export datasheets and objects in several formats for use in other programs. You can also export Access objects to another Access database. Sometimes it is better to leave the data in its original form and location and link to it instead of importing into the database. If the source file is moved or changes its name, or if the linked object changes its name, then the link will break. The Linked Table Manager can help you repair broken links.

A Select query can sort records or pick out certain records by using criteria, or both. 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, Count, 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 really helpful 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 from your brain as to why you chose as you did. You can print table datasheets but you cannot print the table design from Access. A screen capture of the window can be pasted to a graphics program like Paint and printed from there. The Database Documenter feature produces a detailed list of all the properties for a table or a query.

The Object Dependencies task pane shows a tree of dependencies - what objects depend on which other objects. 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.png		 4
1

Skills Covered - by lesson

Design Tables

  • Think through the steps to design and redesign of a table
  • Change field size
  • Add records and use spell check
  • Rename a table
  • Create a table manually, including a primary key 
  • Copy and paste data between tables
  • Copy and paste data from browser to table 
  • Use a foreign key
  • Create a relationship between tables
  • View subdatasheets

Format Fields

  • Create a table
  • Create a relationship between tables 
  • Enter data
  • Set the Format property for a field - lower case
  • Create an input mask
  • Edit data in a field with an Input Mask
  • Create a custom Input Mask for a field
  • Add Format to field definition

Indexes

  • View a tables indexes
  • Create an index using several fields
  • Add an index

Validation

  • Set the Validation Rule and Validation text for a field
  • Save table and automatically test rules 
  • 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

Redesign Table

  • Analyze table structure
  • Change field's data type to a Lookup field
  • Select values using a Lookup combo box
  • Delete a field from datasheet view
  • View subdatasheets

Document a Table

  • Print Relationships
  • Print a table datasheet
  • Use Documenter for a table
  • View dependencies

Import Spreadsheet

  • Import spreadsheet data: new table with wizard
  • Modify table design: field sizes, data types, Attachment field
  • Import spreadsheet data: Named range
  • Append data with copy and paste

Import Text

  • Import data from text file: new table with wizard
  • Modify table design: field sizes, data types,
         Attachment field
  • Import text data and append new records to an existing table

Import Objects

  • Import objects: tables and queries from another Access database
  • Correct import errors/inconsistencies
  • Create Lookup fields from imported objects

Link

  • Link to a table
  • Create relationships between tables
  • Add a Currency data type fields to linked table
  • Type a character not on the keyboard
  • Propagate field changes
  • Break links by changing database name
  • Repair broken links with Linked Table Manager
  • Break link by changing table name

Export

  • Export as text file
  • Export as text file without formatting
  • Export as formatted Excel file
  • Export to another Access database

 

Select Query

  • Open Query Design View
  • Add all fields from a table at once to grid
  • Clear the query design grid
  • Add fields to query design grid
  • Sort with multiple fields
  • Move columns in a query design grid
  • Use criteria in a select query
  • Use multiple criteria rows
  • Use OR to combine criteria in a select query
  • Use NOT to elimiate results in a select query
  • Find Null or Not Null values
  • Use Like operator
  • Name and save a query

Calculated Values-Text

  • Copy, paste, 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-Numbers

  • Create a calculated number field
  • Format a calculated value as part of the expression
  • Create a conditional value with IIf statement
  • Use nested IIf statements

Calculated Values-Totals

  • Use the Total row to count records
  • Open a second database while the first remains open
  • Use the Total row to sum and to average
  • Group a total's results on a calculated field

Calculated Values-Dates

  • Write a simple calculation - Length of Service
  • Use the Format function
  • Use the Expression Builder with DateDiff function
  • Get Help for a function from Expression Builder
  • Correct for where we are in the year
  • Use the DateAdd function
  • Group and count records
  • Use the DatePart function

Parameter Query

  • Create a parameter query and Include choices in the parameter text
  • Create a parameter query with BETWEEN and two parameters for one field 
  • Set data type for a parameter

Make-Table Query

  • Create a Select query
  • Change query to a Make-Table query and run it
  • View the results and set Primary Key.

Append Query

  • Add a record to related table
  • Design a query to append partial records
  • Create a Select query
  • Change the query to an Append query
  • View results and complete partial records added by Append query

Delete Query

  • Check relationships first - are cascading deletions allowed
  • Check the table and its related records - what should be deleted
  • Create a select query
  • Change the query to a Delete query and run it
  • Verify the deletion

Update Query

  • Create a Select query that uses OR in criteria
  • Change the query to an update query and run it
  • Verify that the updates were done

Crosstab Query

  • Create a Crosstab query with the Crosstab Query Wizard

Document a Query

  • Use Documenter for a query
  • View object dependencies for a query
  • Capture a screen shot of object dependencies, paste to Paint, and crop
  • Add text to a screen shot and print
  • Document Query Design View with a screen shot and print
  • Backup, compact and repair a database

Skills in Exercises

Ex. 3-1 Game Scores

  • Save and use a copy of a database
  • Change properties of fields
  • Create relationships
  • Create and use an update query 
  • Create and use a parameter query 
  • Compact and repair database
  • Create select and totals queries with criteria
  • Create a calculated field in a query
  • Print documentation - datasheets, documenter report, relationships, object dependencies

Ex. 3-2 Language Arts Grades

  • Rename the database
  • Create relationships
  • Create new tables
  • Change field properties
  • View subdatasheets
  • Create a select query with criteria
  • Create a parameter query
  • Create calculated fields with text
  • Use IIf
  • Create an update query
  • Create a Totals query with parameters
  • Create a Crosstab query
  • Use calculated fields with numbers
  • Create a MakeTable query
  • Create an Append query
  • Delete a table
  • Delete a relationship
  • Repair a broken query
  • Icon: New Skill Use the Round function 
  • Compact and repair the database
  • Document the database

Ex.3-3 Scouts

  • Import data and objects from external sources in different formats
  • Create relationships 
  • Correct imported queries
  • Export results of a query 

Important Terms

action query

aggregate function

Allow Zero Length property

Append query

Avg function

blank

calculated field

calculated value

Caption property

cascade delete

cascade update

Character Map

compact

concatenate

constant

Count function

criteria

Crosstab query

DateAdd function

DateDiff function

DatePart function

default value

Delete query

documentation

Documenter

expression

export

Expression Builder

field

field size

foreign key

Format function

Format Property

frozen column

function

Group By

identifier

IIf function

import

index

Input Mask Property

Like operator

link

Linked Table Manager

Lookup

Make-Table query

Max function

Min function

nested

normalized

Null

object

Object Dependencies

operator

Paint

parameter

Parameter query

Paste Errors

primary key

propagating nulls

property

referential integrity

relationship

required property

run

screen capture

screenshot

Select query

square brackets

string

subdatasheet

Sum function

syntax

Total row

Update query

validate

Validation Rule

Validation Text

wild card character

wizard

zero-length string

Zoom window