Jan's Working with Databases

Basics: Summary

The simplest database is a single table. A relational database normally contains several tables that are related by sharing fields. You can view the records in a table as a datasheet in rows and columns or as a form or report. You can filter and/or sort the records. A report can include groups and calculated values like totals and averages.

Each field in a table has a number of properties, depending on the data type. You create new fields and select data types and properties in the Table Design View.

The Lookup tab for a field in Table Design View lets you create a Lookup list. In a datasheet or form you choose a value for this field from the list instead of typing in a value. The Lookup Wizard is in the list of data types. It walks you through the choices for the type of list and the source for the choices.

Sorting arranges records in a new order based on a particular field or fields. Filtering hides records that do not match the criteria that you chose. Filters add to each other so that you can filter the already filtered results. Sorting with the ribbon buttons applies a new sort each time.

A query can sort, filter, and rearrange fields in more complex ways that the ribbon buttons can handle. A query can be the source for a form or report. The Query Design View shows the tables that the query uses at the top and below are columns for the fields that are used by the query. There are rows for sorting and criteria.

A form is used to ease the entering and editing of records. The fields can be arranged to fit tightly together to save space and to avoid horizontal scrolling.

A report is used primarily for printing records and calculations, like totals and averages.

When printing database objects you need to check carefully to see what will print. You can select several adjacent records and print just the selection. For non-adjacent records you need to use a report based on a query that selects just the records that you want.

Access provides several wizards to help you create database objects, including a table, query, form, and report. The AutoForm and AutoReport buttons create a simple version that shows all of the fields from the source table or query. The Form and Report Wizards offer more choices to help make some tricky choices easier. A control in the Design View of a form or report displays data from a field. Each control has an attached label. The default size for a control may not be large enough. You can resize controls by dragging the handles in Design View or Layaout View or by editing the property's Width and Height in the Property Sheet. You can rearrange the positions of controls by dragging. You can add titles, text, lines, and images in Design View.

Changing the table's design after records have been entered may cause data to be cut short or lost entirely.

Relationships connect tables through a shared field. The Relationships window displays the tables of your choice and displays a join line connecting the matching fields. If you tell Access to enforce referential integrity, you will not be able to change or delete items that are referred to by other tables. This will help you keep your records consistent.