|
Ch 2 -- Table and Database Design
- 2 -
Table and Database Design
Before you enter any information in Access, you have to create a table
somewhere. A table is the foundation for all queries, forms, and reports. The container
for all these objects is the database.
In this part, you find tasks showing how to create your database file and the
tables that belong to the database. You follow steps for creating the basic building
blocks of a table--fields. In addition, you find reference information for setting
the data type, format, default value, size, and other properties of fields.
To speed up data retrieval and link to other tables, you can also index the
fields, set their primary keys, and create relationships between tables.
Data: Blanks, Nulls, and Zero-Length Strings
When entering information into your database records, you can leave data out of
a field if it is not available. However, a blank field can mean more than
one thing. It might signify that you don't know what the field's value is or that
a value for the field doesn't exist. You can leave a field blank if you don't know
what the value is (the value in this field is actually called a Null value).
If you set the Zero-length property, you can enter "" (two quotes)
in the field to indicate there is no value.
Steps
- 1. To set the Zero-Length property, open the table in Design
View and go to the field. Change the Allow Zero Length property to Yes.
2. If you want to display text to inform you which fields have null versus
zero-length strings, click in the Format property. For text data types, enter
Text Format;Null Format; Zero-length Format. Where Text Format
is any formatting characters you would use for text, Null Format is what you
want nulls to look like, and Zero-length format is what you want zero-length
formats to look like. An example is @;"Unknown";"None".
For number data types, there are four options in the Format property: Number Format;
Negative Format; Null Format; Zero-Length Format.
3. When finished, close and save the table design.
Data: Format
In addition to decimal places (see "Decimal Places"), you can change
the format of a number so that all numbers look consistent in your table.
Numeric formats include dollar signs, percent signs, and commas. Date formats include
spelling the months or using numbers for the month, and how many digits to use for
the day and year. Text formats include capitalization.
Queries, forms, and reports also enable you to change the
format places. On these objects you can change Format by right-clicking the field
or control in Design View and making the change on the Property
sheet in the forms and Reports part of this book. (See "Controls: Properties
Change " in the Forms and Reports part of this book.)
Steps
- 1. Click a table name in the Tables tab of the Database window,
then choose the Design button.
- 2. Choose the field and click in the Format property on the bottom
half of the Table Design window.
- 3. Choose one of the choices from the drop-down list.
- 4. Click the Close (X) button on the Table Design window and choose Yes
to save the changes to the table design.
TIP: Text format types do not appear in
a drop-down list. Type > to convert your entry to all uppercase. You can
also press F1 while you are in the Format property box for more detailed codes on
all data types.
Data Types: Changing
When you create a table, you specify the data type for each field
in the Table Design View. Text, Number, Date/Time, Currency, and Yes/No data
types are self-explanatory. AutoNumber, formerly called Counter in previous
versions of Access, increments each record by one. While Text fields can only
contain up to 255 characters, Memo can include over 65,000 characters. OLE
Objects enable you to insert graphics, sounds, and other data types. The Lookup
data type will enable you to choose from a list of options. (See "Lookup Columns:
Create with Wizard.") The Hyperlink data type enables you to launch an
Access object (a table, form, query, and so on), another file in another application,
or go to a Web site. (See "Hyperlinks: Create Field.")
CAUTION: You can change a field's data
type but, depending on the particular conversion, this process can lead to data loss.
Steps
- 1. Click the Tables tab of the Database window, click the table
name and choose the Design button.
- 2. Move to the field and click the Data Type column, then choose the data
type.
- 3. Close the Table Design window and choose Yes when prompted to
save your change.
Data Types: Setting Defaults
When you first create a field in Table Design View, the field is automatically
set to text and the default text field size is 50. If most of your fields are not
text or are a different size, you can change these defaults.
Steps
- 1. Choose Tools, Options and click the Tables/Queries tab.
- 2. In the Default Data Type drop-down box, choose the data type
you use most.
- 3. Type your most used size for text in the Text box.
- 4. In the Number drop-down box, choose the most used size for numbers.
- 5. Choose OK when finished.
NOTE: When you import data from another
source, you can type which names of fields will automatically be indexed in
the AutoIndex on Import/Create text box.
Database: Create Blank
A blank database is a database file that contains no objects or
data. It is an empty shell that you will use to add new tables, queries,
forms, and reports. If you want Access to create some of your tables,
forms, and other objects, see "Database: Create New with a Wizard."
Steps
- 1. In a blank Access window or while the Database window from another
database is showing, choose the New Database button on the toolbar.
- 2. Double-click the Blank Database template in the General tab of the
New dialog box.
- 3. Enter a name in the File New Database File Name text box,
specify the storage location of the file in the Save In drop-down list
box, then choose the Create button.
NOTE: You can also press Ctrl+N to start
a new database from anywhere in Access. After you give the database a name, Access
closes the database that you were working on and opens a blank Database window.
Database: Create New with Wizard
You can create a new database that is blank or let the Database Wizard create
one for you that contains the objects you specify for it. The task, "Database:
Create Blank" describes the former process. This task describes the use of the
Database Wizard. Both procedures begin the same way.
When you use a wizard, Access enables you to choose from sample fields and then
create the tables, queries, forms, reports, and switchboards
for you. A switchboard is a form with buttons that help a user navigate through
the database. You can even have Access populate your database with sample
data to help you learn how different features work in the sample database.
Steps
- 1. When you start Access the Introductory screen is displayed. Click Database
Wizard to open the Wizard.
- If you already have Access opened, click the New Database button on the toolbar.
2. Select the database from the Databases tab of the New Database dialog
box; then choose OK.
- 3. Specify a location for the database file in your file system using
the Save In drop-down list box; enter a new name for the database in
the File Name text box of the File New Database dialog box; then choose
the Create button. Access launches the Database Wizard.
4. Choose Next to view the screen that lets you select additional fields and
sample data.
Click the check boxes next to include optional fields (in italics) or uncheck
any other fields. Click the check box next to the Yes, Include Sample Data if you
want that feature, then choose Next.
- 5. On the next four wizard screens, select the styles you want for your
forms and reports, give the database a name that will appear on the switchboard,
and choose to open the database. On the last step, choose Finish.
NOTE: To see which sample database wizards
are available, look on the Database tab of the New dialog box. These wizards are
installed during the default setup. However, if you do not have the wizards, return
to setup (through your Office or Access CD), choose the Chang e Option button
while Microsoft Access is selected, and check the Wizards box.
NOTE: On the second to last step of the
new Database Wizard, you can include a picture on your reports by clicking the Yes,
I'd Like to Include a Picture check box, then choose the Picture button to specify
a picture file in the Insert Picture dialog box. Several graphic formats such as
bitmaps, icons, the Windows Metafile, TIFF, PCX, PICT, JPEG, GIF, and EPS are supported.
Database: Documentor
Data documentation can help you get organized, especially for large databases.
Database dictionaries describe the database as a whole, each table, and each
field. Access has a Database Documentor that describes these and other parts
of your database for you.
Steps
- 1. From your open database, choose Tools, Analy ze, Documentor.
- 2. When the Documentor dialog box opens, select which objects you
want to document by choosing the Object Type drop down and clicking
the check boxes next to each item in the Objects list.
- If you want to document everything (which can take a while), click the All Object
Types tab and choose the Select All command button.
- 3. When you are finished choosing the objects, choose OK. A preview of
your documentation report appears on-screen. Print your report if desired.
- 4. If you want to save the documentation information into a table, choose
File, Save As Table.
Database: Examples
One of the best ways to learn Access and find ideas for your own databases is
to look at the samples that come with the program. These sample databases might have
been installed when you set up Microsoft Office. If not, you will need to go through
setup. The files are Northwind, Orders, and Solutions. The first database
you should look at is Northwind. Orders and Solutions provide examples for application
development and programming.
Steps
- 1. To open a sample database, click the Open Database button on the toolbar.
- 2. Change the Look In drop-down box to the Samples subfolder of the folder
where the Office directory is installed.
- 3. Double-click Northwind, Orders, or Solutions.
NOTE: The Northwind database includes
a Show Me menu that explains features of the application. Orders and Solutions have
a Show Me button on their toolbars.
Datasheet: Appearance Change
There are numerous ways you can alter the appearance of your datasheet. You must
have the datasheet for that table in view; these settings apply universally
to the table, and not to individual cells. You can modify a table datasheet, query
datasheet, or the Form View of a form.
Steps
- 1. Double-click the table or query name in the Database window
to open its datasheet; or click the View button drop-down arrow and choose Datasheet
View when a form is in view.
- 2. Choose Format, Font to select a new font, font style,
font size, or color.
- 3. Choose Format, Cells and change the gridline, gridline
color, cell appearance, and cell background color in the Cells Effects dialog
box.
- 4. Click the Close box to close the datasheet.
- Access remembers your settings the next time you open a datasheet for this table,
query, or form.
Additionally, you can change the width of a column or the height of all rows in
the datasheet using the Format, Column Width (see "Width of Column"
in the Database Essentials part of this book) or Format, Row Height
commands. Each column can have its own width, but all rows must be the same size.
You can also hide (see "Hide Columns") or freeze (see "Freeze Display
of a Table Field" in the Database Essentials part of this book) columns to help
see the more of the datasheet.
Datasheet: Appearance Defaults
When you use the Format menu to change the datasheet appearance, only the viewable
datasheet changes. (See "Datasheet: Appearance Change.") You can also change
the appearance for all datasheets you have not individually changed.
Steps
- 1. Choose Tools, Options, and click the Datasheet tab.
- 2. Change any of the options for font, background, and gridlines;
choose OK.
Decimal Places
Decimal places are basically the numbers after the decimal point (for example,
25 becomes 25.00 when you add two decimal places). When you work with numbers, having
all related numbers with the same number of decimal places adds to the professional
appearance of your output. Changing the number of decimal places only adds to the
visual appearance of a number, not to its value. Another option is to use the Rnd
(Round) function, which will change the value of a number. If you want to
add commas or other symbols with numbers, change the format property. (See
"Data: Format.")
Forms and reports also enable you to change decimal places. On forms
and reports, you can change decimal places by right-clicking the control in
Design View and making the change on the Property sheet. (See "Controls:
Properties Change" in the Forms and Reports part of this book.)
Steps
- 1. Click a table name in the Tables tab of the Database window,
then choose the Design button.
- 2. Choose the number or currency field and click in the Decimal
Places property on the bottom half of the Table Design window.
- 3. Choose Auto to let Access determine the number of decimal places (usually
two), or type in your own number of decimal places.
- 4. Click the Close (X) button on the Table Design window and choose Yes
to save the changes to the table design.
Field: Caption as Alternate Name
Captions enable you to uniquely name the column header in Datasheet View
(as opposed to calling it by the field name). After you add a caption
to a field, any new queries, forms, or reports will use the
caption as the default for column headers or labels for the field. Queries also have
a caption property for each field.
NOTE: Labels and column headers for existing
queries, forms, and reports do not change when you change the field's caption in
Table Design View.
Steps
- 1. Choose the table in the Database window and choose the Design
button.
- 2. Move to the field to which you want to add the caption. In the lower
half of the Table Design window, click the General tab and click in the Caption box.
- 3. Type text for the caption. When finished, close the table and choose
Yes when prompted to save changes to the design of the table.
Field: Create
You must create a field in a table to use it in a query,
form, or report. A field holds one specific piece of information in
a record. Examples include a company name or a salary. It is better to condense
the information that goes into a field into the smallest unit you can use. For example,
instead of having an entire name in a field, use at least two fields--one for first
name and one for last name. You will then be able to sort, find, and group
information on the last name and use both name fields for mailing labels and letters.
Steps
- 1. Open a new table or choose an existing table in the Database window
and choose the Design button.
- 2. In the top half of the Table Design window, move to a blank row and
type a field name.
- 3. Press Tab and click the down arrow to choose a data type.
- 4. Press Tab again and type a description (which will appear on the status
bar in Datasheet or Form View). If desired, click in the lower half of
the window and set any additional field properties.
- 5. When finished, close the Table Design window and choose Yes
when prompted to save the table design.
NOTE: The description and other field
properties become the defaults for many of the control properties in forms. However,
if you change the table's field properties after you create a form, most of the properties
do not change on the form.
Field: Default Value
You can set a default value for a field in the Table Design View
on the General tab. When you enter a value or expression in that property,
that default value is entered into each new record when you create the record. You
are free to overwrite the default value if you have write privileges for that field.
The default value does not affect any records you enter before you create
the default value. You can have a different default value for the field on
a form than the one you create for the table. (See "Forms: Default Value"
in the Forms and Reports part of this book.)
A common default value would be a state or country (entered as CO or USA).
Another common default would be today's date, which you enter as Date().
Steps
- 1. Click the table name in the Tables tab of the Database window,
then choose the Design button.
- 2. Move to the field and click in the Default Value text box, then
enter your value. Or, enter an expression that evaluates to a value.
3.Click the Save button on the toolbar to save your new database rule.
Because this default value is applied at the table level, the mechanism for entering
the default value operates in a datasheet or form. During an append operation, default
values are not added to the new records that are appended to the table.
TIP: Creating default values is a great
time saver and speeds up data entry. When you have a field that usually has the same
value entered into it, consider setting this property.
Field: Delete
If you no longer need a field, you can delete it. For instance, after you import
a table from another source you can change the table design to add fields for first
name and last name. After you enter these data for these fields for all your records,
you then want to delete the original name field that contained both names.
CAUTION: Be sure that you won't be using
the field again when you delete. All the information in the field is lost for every
record. It will be a large task to find and enter the information for a mistakenly
deleted field. You can use Undo if you immediately notice you deleted the wrong field
or if you choose, when prompted, to not save changes to the database design. However,
this task is so potentially dangerous that you should probably back up your database
first. (See "Backup Data" in the File Management part of this book.)
Steps
- 1. Click the table name in the Tables tab of the Database window,
then choose the Design button.
- 2. Click in the field you want to remove and click the Delete Rows button
on the toolbar.
- 3. When prompted if you want to permanently delete the information, choose
Yes (but only if you really want to).
Field: Description
The Description property provides information or notes about fields in
tables, and queries. Descriptions can be up to 255 characters in length. Descriptions
appear in the status bar while entering data in a field in Datasheet
or Form View.
This propertyx2 is set in the Table Design View for tables, and in the
Field Properties Property sheet in the Query window for queries. (See "Queries
and Filters: Query: Properties" in the Queries and Filters part of this book.)
When you create a control by dragging a field from a Field List, Access copies
the Description property to the control's Status Bar Text property. It then displays
that description in the status bar whenever the insertion point is entered
into that field.
Steps
- 1. Click a table name in the Tables tab of the Database window,
then choose the Design button.
- 2. Click in the Description column of the field.
- 3. Enter a value for the description. You do not need to surround the
description with quotation marks.
- 4. Click the Save button on the toolbar to save your description.
Field: Insert
You can add a field at the bottom of the field names section Table
Design window (see "Field: Create") or you can insert a field in between
existing fields.
Steps
- 1. Click a table name in the Tables tab of the Database window,
then choose the Design button.
- 2. Click in the field below where you want your new field to go and click
the Insert Rows button on the toolbar.
- 3. Enter the field name, data type, description, and any properties
for the field in the field properties section at the bottom of the Table window.
- 4. Click the Close (X) button on the Table Design window and choose Yes
to save the changes to the table design.
Field: Name
You can change the name of a field in your database and your table's data is left
unaffected. However, if you have used the field in a query, form, or
report created prior to the change, you must manually update that control
to reflect the new field name. If you want to see a different name in the column
header of Datasheet View, you can also change the caption property.
(See "Fields: Caption as Alternate Name.")
NOTE: Field names can be up to 64 characters
and include spaces. However, if you are going to upsize your database to a database
server such as SQL, it is better not to include spaces because the
table's field name spaces will be converted to underscores. Any queries, forms, or
reports based on the tables will produce errors and will need to be modified.
Steps
- 1. Click a table name in the Tables tab of the Database window,
then choose the Design button.
- 2. Click the name of the field and edit that name.
- 3. Click the Close (X) button on the Table Design window and choose Yes
to save the changes to the table design.
Field: Rename in Datasheet View
To rename the field in a query, change the name of the field in
the Query Design grid. That new name provides the column name for the field
in Datasheet View, unless the Caption property has been set
(in which case, the caption is used). The renamed field also provides the name of
the Control Source for any control in a form or report that
is based on that query.
Steps
- 1. Open the table in Datasheet View.
- 2. Double-click the column header of the field of interest and enter the
new name of the field.
Field: Set Properties
You can set many of the field properties in Table Design View. Properties
include name, data type, description, field length, validation rules,
default values, and whatever you see on the General or Lookup tabs in table
design.
Steps
- 1. Click a table name in the Tables tab of the Database window,
then choose the Design button.
- 2. Click the field in the Table Design View.
- 3. Change the properties on the same row of the field name or press F6
and change the properties in the lower half of the window. The properties include
field size, format, decimal places, caption, and default value among others. The
actual properties change depending on the data type. For a description of each property,
see the blue text on the right or click in the property and press F1.
- 4. Click the Close (X) button on the Table Design window and choose Yes
to save the changes to the table design.
When you click in the text box for some of the properties, there is a drop-down
arrow representing a list of choices. Click the arrow and then the desired item in
the list. Some properties (for example, Input Mask) also have a build button (...)
on the right side of the text box. You can click this or the Build button
on the toolbar to bring up a dialog box with examples. Another option is to begin
typing in the text box. Access will automatically complete the entry with the first
available option where the first letter matches your entry. For example, in the data
type text box, type n to select number.
TIP: Double-click any property that has
multiple choices either in the Data Type or lower section of the design window. This
will cycle through the available list of choices. (This is a general feature of Property
sheets.)
Field: Size
Field size for text data type fields determines the maximum number of characters
you can enter for a field. You can set the field size for text up to 255 characters
and the default is 50 characters unless you change the default. (See "Data Types:
Setting Defaults.")
For numbers, field size determines the range of numbers you can enter and whether
or not the number can include decimal places. Generally, you want to set the smallest
possible field size for text or number but still include all possibilities you might
enter. With smaller field sizes, your database file will be smaller and quicker.
Steps
- 1. Click a table name in the Tables tab of the Database window,
then choose the Design button.
- 2. Click in the field and then click in the Field Size box.
- 3. Type in a number from 1 to 255 for text data types. If your data type
is numeric, choose one of the field sizes shown in the following table from the drop-down
list.
- 4. Click the Close (X) button on the Table Design window and choose Yes
to save the changes to the table design.
You want to choose one of the following numeric field sizes that will accommodate
your data using the smallest number of bytes possible.
Numeric Field Sizes
| Option |
Description |
Byte Size |
| Byte |
Numbers 0-255 without decimals |
1 |
| Integer |
Numbers from about -32,000 to +32,000 with no decimals |
2 |
| Long Integer |
Very large numbers without decimals (+/- 2 billion) |
4 |
| Single |
Large numbers with decimals (up to 38 digits before or after the decimal place) |
4 |
| Double |
Largest possible numbers with decimals |
8 |
NOTE: When you create relationships
between fields from different tables, all data types and field sizes for numbers
must match. The exception is an AutoNumber field. Because the foreign key
field will not be an AutoNumber field, the related field in the second table should
have Long Integer Field Size.
TIP: If you often calculate with a field
that has between one and four decimal places, consider using Currency data type instead
of Single or Double. Currency uses the faster fixed-point calculation rather than
floating point calculations.
Gridlines: Turning On and Off
Access normally prints and displays vertical and horizontal gridlines in
Datasheet View. If you want, you can turn these gridlines off. You can also
change the background or font of the cells. (See "Datasheet: Appearance Change.")
Steps
- 1. Open a table, query, or form in Datasheet View.
- 2. Choose Format, Cells.
- 3. Uncheck one or both of the Horizontal and Vertical check
boxes in the Gridline Shown section of the Format Cells dialog box; choose
OK.
Hide Column
In some cases you might not want to see all the columns of the datasheet. Perhaps
your display is too wide, you don't need to enter all information, or you only want
to see relevant information to your task. Another option to help you navigate with
many columns is to freeze columns. (See "Freeze Display of a Table Field"
in the Database Essentials part of this book.)
Steps
- 1. Open a table, query, or form in Datasheet View.
- 2. Right-click the column header and choose Hide Columns.
To return a hidden column to view, use the Format, Unhide Columns
command and check the box next to the column you want to see.
CAUTION: When hiding columns from view,
be careful that you don't inadvertently neglect their data entry.
Hyperlinks: Create Field
You can use hyperlinks in forms and datasheets to jump to the location described
in that hyperlink. Locations can be other objects in Access databases,
documents created by Word, Excel, or PowerPoint, and documents on the Internet or
an intranet.
Access contains a new data type called a hyperlink field. A hyperlink field
contains the text and numbers that comprise a hyperlink address, which is the path
to the object, document, or Web page. A hyperlink address can also be an URL (Uniform
Resource Locator) for an Internet or intranet address. Access recognizes a hyperlink
address from the entered syntax.
Steps
- 1. Open the table in the Design View by selecting it in the Database
window on the Table tab and clicking the Design button.
- 2. In the field list, enter the field name for the new hyperlink field.
- 3. Tab to the Data Type column and select the Hyperlink data type.
4.Click the Close (X) button on the Table Design window and choose Yes
to save the changes to the table design.
Index: Create a Composite Index
Access enables you to create indexes based on two or more fields in your table,
up to a limit of 10 fields. You can specify that a composite index is unique
and use it as a primary key, or use that composite index to speed up sorting
or searching through your data. Access does not allow you to index on expressions.
However, in many cases a single field index will be sufficient. (See "Index:
Create Based on a Single Field.") A multiple field index can also make up a
primary key. (See "Index: Primary Key.")
Steps
- 1. Click a table name in the Tables tab of the Database window,
then choose the Design button.
- 2. Click the Indexes button on the toolbar.
- 3. Type an index name in the Index Name column of the Indexes window.
- 4. In the Field Name column, enter the first field in the index.
- 5. Add additional fields below that line, without naming another index,
up to ten fields.
To remove an index, click the Indexes button again, select the rows making up
the index and press Delete. When searching or sorting on the non-indexed field, Access
now takes longer. If you have no index at all in a table, Access orders the records
in the order you enter them in the table.
Index: Create Based on a Single Field
You can index a single field to serve as a method for ensuring unique values,
to sort your data, or to speed up search and retrieval operations.
When you index a field, you have two options. Yes (No Duplicates) means that you
will not have any entries that match in more than one record. Yes (Duplicates
OK) means that entries can match.
Steps
- 1. Click a table name in the Tables tab of the Database window,
then choose the Design button.
- 2. Click the field in the table Design View and set the Indexed
property in the General tab to one of the Yes options.
Index: Primary Key
A primary key is the index used to uniquely identify records in a table.
Every table should have one primary index, although other unique indexes can be defined
(as so-called candidate indexes). Often, the primary index is used to establish a
relationship with a child table. (See "Relationships Between Tables.")
The field that the primary key is related to in the other table is called a foreign
key. A primary key can contain one or more fields.
Steps
- 1. Click a table name in the Tables tab of the Database window,
then choose the Design button.
2. Click the field in the Table Design View. If you want more than
one field to make up the primary key, hold down Ctrl and click the field selectors
of the other fields.
3. Click the Primary Key button on the toolbar to make the selected field(s)
the primary key.
To remove a single field primary key, with that field selected in the Table Design
View, click the Primary Key button on the toolbar again, or delete the index from
the Indexes window.
Index: Set Index Properties
Indexes are listings of values or expressions in a field
or combination of fields. An index in a database operates just like the index
in a book. Indexes point to where something is located.
Indexes are particularly valuable in a number of database operations. They speed
up finding and sorting information when you perform those operations later and also
change the sorted view of your table. Indexes are necessary to match the data in
a field of one table to a field in another, and thus provide the means for
relating one table to another.
Steps
- 1. Click a table name in the Tables tab of the Database window,
then choose the Design button.
- 2. Click the Indexes button on the toolbar.
In the Indexes window you can set index properties: the Index Name (which
by default takes the field name but can be changed), the Sort Order, and whether
the index is Primary, Unique, or Ignores Nulls. The Ignore Nulls option makes the
index smaller and speeds up searching records.
Input Mask: Phone Number and Other Entries
As you enter information in Text and Date data type fields, you might want
certain symbols to appear. For example, a phone number has parentheses and a dash.
You can manually type these symbols in each text field or you can create an input
mask to automatically do the job. An input mask can also verify each character
as you type it. To change the display of an entry after you type the entry and move
out of the field, you can also format the field. (See "Data: Format.")
TIP: The input mask wizard gives examples of
the most common input masks you might want. These include phone number, social security
number, long zip codes, passwords, and date and time values.
CAUTION: Make sure your other field properties
(such as Format, Default Value, Validation Rule, and Required) do not conflict with
your input mask.
Steps
- 1. Click a table name in the Tables tab of the Database window,
then choose the Design button.
- 2. Click the field and click in the input mask property at the
bottom of the Table Design window.
- 3. If you want to use a predefined input mask, click the Build button
to the right of the text box and choose one of the samples in the Input Mask
Wizard; choose the Next button.
- 4. On the next two steps of the Input Mask Wizard, choose the placeholder
character that you want to appear as the user types each character and whether you
want to store the symbols with the table. Choose the Finish button when done.
TIP: It is generally better not to include
symbols with the table because the entries will be shorter (and thus take up less
room in your database). However, if you will be exporting this data to a spreadsheet
or other database, you might want to include the input mask symbols.
NOTE: You can also type Input Mask characters
directly in the property box in Table Design View. For a description of the
acceptable characters, click in the Input Mask box and press F1.
Lookup Column: Create with Wizard
There are many instances when you might want to look up information to place in
a field. This is especially true when you have codes representing values. Instead
of trying to remember the codes, you can create a lookup column that enables
you to choose something like the employee name rather than remember their employee
identification number.
Steps
- 1. Click a table name in the Tables tab of the Database window, then choose
the Design button.
- 2. If necessary, type the name for the field or go to an existing field.
Choose Lookup Wizard as the data type.
- 3. If the data is from another table, identify that you want to use an
existing table in the first step of the wizard and choose the table in the second
step.
- 4. In the third and fourth step of the Lookup Wizard dialog box,
double-click the fields that you want to appear in the lookup list and choose whether
you want to hide the key column (usually an ID column).
- 5. Give the column a name in the last step of the Wizard and choose Finish.
Access will prompt you to save the table.
NOTE: The first step of the Lookup Wizard
also asks you if you want to type the values rather than use an existing table. It
is generally a better idea to use a table because you can use it for more than one
combo or list box.
Lookup Column: Properties
After you create a lookup column (see "Lookup Column: Create with
Wizard"), you might want to change or verify the lookup properties for the field.
The Lookup Column properties identify the source and organization for your drop-down
menu. One important lookup property is the Row Source, which can be a query
or SQL statement. You can edit the SQL statement by clicking the build button
(...) to the right of the Row Source text box and then manipulate the query builder
just like a normal query. (See "Query: Create with Design View" in the
Queries and Filters part of this book.)
Steps
- 1. Click a table name in the Tables tab of the Database window, then choose
the Design button.
2. Choose the lookup field and click the Lookup tab in the Field Properties
section of the Table Design window.
3. The first property--Display Control--is usually set to Combo Box.
This enables you to choose a drop-down arrow or type in the value. If you choose
List Box, you only choose from the list. If you choose Text Box, you
remove the lookup portion of the field and only type in the value in the field.
4. If you told the Lookup Wizard to use an existing table or query, the second
property--Row Source Type--is Table/Query and the third property will be the name
of a query or an SQL statement that you can edit by clicking the build button (...)
on the right. If you typed a list of values in the Lookup Wizard, the values that
you can edit appear in this area.
5. The Bound Column stores in the table the value from the specified
column in Row Source. You might need to change the Column Widths property so you
can see the entire columns from your Row Source.
- 6. Click the Close (X) button on the Table Design window and choose Yes
to save the changes to the table design.
Relationships Between Tables
When you define a relationship between two tables, you match the
values in one table to values in another table. In order to create a relationship,
one or both of the tables requires that the values used in the match be unique. Normally
an index (usually the primary key) in the controlling or parent table
is used, and a field (called the foreign key) in the child table
is matched.
In addition to defining a relationship, you set referential integrity rules
in the relationship dialog box. When you enforce referential integrity, you
say that you do not want any orphan records in the child table. Orphans occur when
no records are matched to the parent table. If you choose Cascade U_pdate
Related Records, whenever you change the ID field in the parent table, the
field in all corresponding records change in the child table. If you choose Cascade
Delete Related Records, you will delete any children records
when you delete the parent record. If you choose neither while enforcing referential
integrity, you will be unable to update the ID field or delete the record when child
records exist.
Steps
- 1. With the Database window showing, click the Relationships button
on the toolbar to open the Relationship window.
- 2. Click and drag a relationship between a field from the parent table
and the field in the child table.
- 3. The Relationships dialog box opens. In the lower half of the
window, choose whether you want to enforce referential integrity and how the child
table will be updated.
- 4. Finish creating the relationship by choosing OK and closing the relationship
window.
The relationship is represented by a line between the two tables. You can select
a relationship and press the Delete key to remove it. You can also right-click the
line to view the shortcut menu, and select Edit Relationships to open the
Relationships dialog box. A command button on the Relationships dialog box is join
type. You can choose this to set the default join type for queries. (See "Tables:
Combine with Join" in the Queries and Filters part of this book.)
Status Bar: Display User Message
The description property of a field appears on the status bar when
you are in Datasheet View or Form View. The description becomes the
default for the Status Bar property on a form that you can modify. (See "Controls:
Properties Change" in the Forms and Reports part of this book.)
Steps
- 1. Click a table name in the Tables tab of the Database window, then choose
the Design button.
- 2. Choose the field, move to the Description column and type what you
want to appear in the status bar.
3.Click the Close (X) button on the Table Design window and choose Yes
to save the changes to the table design.
NOTE: If the status bar does not appear
on your screen, choose Tools, Options, View tab, and check the Status
Bar check box in the Show section.
Table: Create by Table Wizard
The Table Wizard is a fast way of creating tables. It lets you structure tables
based on fields in existing tables, create rudimentary table relationships, and specify
a primary key.
If you have existing data, you can also import or link the information to create
a table. (See "Import Data" in the File Management part of this book.)
You can also create a table by going directly to design window (see "Table:
Create in Design View") or by working in Datasheet View (see "Table: Create
in Datasheet View").
Steps
- 1. Click the Tables tab in the Database window, then choose New and then
select Table Wizard in the New Table dialog box, then choose OK.
- 2. Click the Business or Personal option button to view a set of sample
tables.
- 3. Select the table(s) you want to view fields from in the Sample Tables
list box; then move the fields of interest from the Sample Fields list box
to the Fields in My New Table list box; choose Next.
- Use the Rename Field button to rename any selected field you add. Access uses
the same data type for your fields when you rename a field.
- 4. Enter a name for the table in the text box; select either Yes for the
wizard to set a primary key, or No if you will set the primary key; choose
Next.
- 5. Select any desired relationships in the My New <Tablename> Table
Is list box; then choose the Next button.
- 6. Select from one of the following: Modify the Table Design; Enter Data Directly
in the Table; or Enter Data Into the Table From a Form that the Wizard Creates
for Me. Choose Finish.
Access creates the new table and saves it to disk. If you select to modify the
table design, you view the Design window (see also "Table: Create in Design
View"). For the Enter Data Directly selection in the last step, you see a Datasheet
window. For the form selection, a form is created for you.
Table: Create in Datasheet View
The datasheet method is a very fast method for creating tables, but is limited
in its capabilities. It is best used for small tables where you will add features
later to the table design. It does not create table relationships, nor does it provide
for data validation or other table properties.
Steps
- 1. Click the Tables tab in the Database window, then choose the
New button.
- 2. Select Datasheet View in the New Table dialog box.
- A datasheet with 20 columns and 30 rows appears with default field names.
- 3. Rename the column headings by double-clicking them and entering your
field name(s); press Enter or click another column or value in the datasheet.
- 4. Enter data into the datasheet; each column is a field, each row is
a record.
- 5. Click Save on the Table Datasheet toolbar. Enter the name of the table
in the Table Name text box in the Save As dialog box, then choose OK.
- An alert box is posted asking you if Access can create a primary key;
click Yes if you haven't created a field with unique values that can identify
each row of your data-sheet (records in the table); click No if you have created
such a field.
NOTE: Use a consistent style of data within
a column for dates, times, numbers, and so on, so that Access can create a data
type and display format based on the values it sees you enter.
TIP: If you need more than 20 columns,
click a column to the right of your new field, then select the Column command
from the Insert menu. Access will automatically add rows after the 30th
record.
Access creates the new table and saves it to disk. When you have Access create
the primary key, it creates an AutoNumber field that has sequential numbers
entered into it.
Table: Create in Design View
A convenient place to create the structure of your database tables is Table Design
View. This is where you add and remove fields, and it serves as a convenient
venue for getting an overview of the properties associated with your fields and table.
Steps
- 1. Click the Tables tab in the Database window, then choose New.
Select Design View in the New Table dialog box, then choose OK.
- 2. Enter a name for a field in the Field Name column, then Tab
and enter the data type in the Data Type column.
- 3. Enter into the Description column the information you want displayed
in the status bar when the insertion point is in that field in the
table. Enter Field Size (number of characters), Format and Input Mask (display
and allowable characters), Caption (for the Datasheet view), Default
Values, Validation rules, and other properties in the General section.
- 4. Click the next blank line of the field grid and create the next
field in your database; then repeat Step 5. To insert a field between two other fields,
click the Insert Rows button on the Table Design toolbar.
- 5. To select the field you want to use to create a primary key,
click the field selector to the right of the field name. Or, select multiple fields
for a compound primary key by holding Ctrl and clicking each field selector; then
click the Primary Key button on the toolbar.
- 6. Click Save on the Table Design toolbar; enter a name for the
table in the Table Name text box of the Save Table dialog box and choose OK.
Access creates the new table and saves it to disk.
NOTE: You don't have to assign a primary
key, but it is recom-mended. Make sure that the order of a compound primary key is
correct. You can change the order by clicking the Indexes button on the toolbar and
reordering the field names in the index that comprises the Primary Key. However,
a compound primary key is used infrequently compared to a single field primary key.
Table: Modify Design
To modify a table, you must select that table in the Database window
and open the table in the Table Design View. In this view you can add or remove
fields; change field names; change a field's data type; and add, modify,
or delete descriptions, field properties, and table rules.
CAUTION: Pay particular attention to modifying
the data type of an existing field. When you change data types, there is the potential
for data loss due to data type mismatch. Your previous field's data may be truncated
or discarded completely. Once it is gone, it is gone forever. Therefore, it is a
good idea to make a backup of your database or table before you change the design
of a table. (See "Backup Data" and "Database Object: Copy" in
the File Management part of this book.)
Steps
- 1. Click a table name in the Tables tab of the Database window, then choose
the Design button.
2.Make your changes or additions in the Table Design View.
3.Choose Insert, Rows to add fields, or Edit, Delete Rows
to remove fields.
- 4. Choose View, Indexes to create or modify table indexes,
or View, Properties to add or alter table properties.
- 5. Enter any General field properties like Captions, Default Values,
Format, Input Masks, Validation Rule, Validation Text, Allow Zero
Length, Required (mandatory data entry), and so on, that you want.
- 6. After you have finished modifying your table, select File, Save.
Or, to save the resulting table as a different file, select File, Save As;
name and locate your table in the file system using the Save As dialog box;
then choose OK.
Access saves your table to disk. If you create a new table, that table's name
appears in the Table tab of the Database window.
Table: Properties
A number of important table properties can be specified that affect how
data is stored and accessed. Two different groups of properties can be accessed.
The first set of properties are object properties that are the same for queries,
forms, reports, macros, and modules. They include the object
description, whether you want to hide the object in the database window, and whether
you want to replicate the object. (See "Replication: Create Replica" in
the File Management part of this book.)
The second set of properties you access from inside of Table Design View. You
can also set the table description here. Other table properties you can set in Table
Design View include a sort order, whether you want to filter out records,
and a validation rule involving more than one field and the rule's associated error
message. (See "Validate Data: Record Validation.")
Steps
- 1. Right-click the table in the Table tab of the Database window and select
the Properties command from the shortcut menu.
- 2. Enter the description and whether the table is hidden. Or, right-click
in the Table Design View (to the left of the leftmost column header of the
field grid), and select the Properties command in the shortcut menu.
- There you can set validation rules and validation text, description, the
field to sort the table by, and how you want to filter the table. An example
of a filter would include a name of a field, a comparison sign (equals, greater than,
less than), and a value. For example, Title = "Sales Manager".
Of the two different types of Properties sheets, the latter is more useful and
used more often. When you open the table from the Database window the Filter and
Order By properties are not applied. Click the Apply Filter button to see the results.
Validate Data: Field Validation
You can create rules for a field where the values you enter must fall in a specified
range. Otherwise the data is not acceptable. Most likely, the user made a typing
error. If you need to use a field name in the expression, you will need to
change the Table's Validation Rule property. (See "Validate Data:
Table Validation" in the Table and database part of this book.)
Examples of validation rules include >100 and between 0 and 50
for number fields, or >Date() (greater than today's date). See the Criteria
sections in the Queries and Filters part of this book and Expression
sections in the Calculations part of this book.
Steps
- 1. Click the table name in the Tables tab of the Database window,
and then choose the Design button.
- 2. Move to the field and click in the Validation Rule box in the
bottom of the Table Design window and enter an expression.
- 3. If you want an error message to appear if this rule is violated, type
the message in the Validation Text box.
- 4. Click the Save button on the toolbar to save your new database rule.
When you enter a value in that field, the value is allowed if the expression is evaluated
as "True."
Validate Data: Record Validation
You can create a table validation rule to validate the data entered into
two or more fields in a record. When you move off the record, Access
checks that the table validation rule is not violated. You cannot leave the
record without either removing the record's data or fixing a record so that it conforms
to record validation.
You enter the record validation rule in the Properties sheet for the table.
An example of a table validation rule is:
[ShipDate]>=[OrderDate]. This means that the shipping date has to
be greater than or equal to the order date.
Steps
- 1. Click the table name in the Tables tab of the Database window,
then choose the Design button.
- 2. Click the Properties button on the toolbar and click in the
Validation Rule box and enter an expression.
- 3. If you want an error message to appear if this rule is violated, type
the message in the Validation Text box; click the Close (X) button to close
the Property sheet.
- 4. Click the Save button on the toolbar to save your new database rule.
Validate Data: Required Field
Some information in a table is so important that the record would
be useless without the information. This is often the case with name fields.
You might want to force yourself or another user to enter information in these fields
before they can leave the record.
Steps
- 1. Click a table name in the Tables tab of the Database window,
and then choose the Design button.
- 2. Go to the field you want to require.
- 3. In the field property area of the Table Design window, set the
Required property to Yes.
4.Click the Close (X) button on the Table Design window and choose Yes
to save the changes to the table design.
If you are missing data in this field after you change the Required property to
Yes, Access will warn you that the existing data violates the rules you just made.
After you save the table, go back and add the missing data.
|
