 |
Introduction to Tables
Tables are grids that store information in a database similar to
the way an Excel worksheet stores information in a workbook. Access
provides three ways to create a table for which there are icons
in the Database Window. Double-click on the icons to create a table.
![[Database Window]](images/blankdb.gif)
the Database Window
- Create table in Design view will allow you to create
the fields of the table. This is the most common way of creating
a table and is explained in detail below.
- Create table using wizard will step you through the creation
of a table.
- Create table by entering data will give you a blank datasheet
with unlabelled columns that looks much like an Excel worksheet.
Enter data into the cells and click the Save button. You
will be prompted to add a primary key
field. After the table is saved, the empty cells of the datasheet
are trimmed. The fields are given generic names such as "Field1",
"Field2", etc. To rename them with more descriptive
titles that reflect the content of the fields, select Format|Rename
Column from the menu bar or highlight the column, right-click
on it with the mouse, and select Rename Column from the
shortcut menu.
Create a Table in Design View
Design View will allow you to define the fields in the table before
adding any data to the datasheet. The window is divided into two
parts: a top pane for entering the field name, data type, and an
option description of the field, and a bottom pane for specifying
field properties.
![[Design View]](images/designview.gif)
- Field Name - This is the name of the field and should
represent the contents of the field such as "Name",
"Address", "Final Grade", etc. The name can
not exceed 64 characters in length and may include spaces.
- Data Type is the type of value that will be entered into
the fields.
- Text - The default type, text type allows
any combination of letters and numbers up to a maximum of
255 characters per field record.
- Memo - A text type that stores up to 64,000
characters.
- Number - Any number can be stored.
- Date/Time - A date, time, or combination of
both.
- Currency - Monetary values that can be set
up to automatically include a dollar sign ($) and correct
decimal and comma positions.
- AutoNumber - When a new record is created,
Access will automatically assign a unique integer to the record
in this field. From the General options, select Increment
if the numbers should be assigned in order or random if any
random number should be chosen. Since every record in a datasheet
must include at least one field that distinguishes it from
all others, this is a useful data type to use if the existing
data will not produce such values.
- Yes/No - Use this option for True/False, Yes/No,
On/Off, or other values that must be only one of two.
- OLE Object - An OLE (Object Linking and Embedding)
object is a sound, picture, or other object such as a Word
document or Excel spreadsheet that is created in another program.
Use this data type to embed an OLE object or link to the object
in the database.
- Hyperlink - A hyperlink will link to an Internet
or Intranet site, or another location in the database. The
data consists of up to four parts each separated by the pound
sign (#): DisplayText#Address#SubAddress#ScreenTip. The Address
is the only required part of the string. Examples:
| Internet hyperlink example: |
FGCU Home Page#http://www.fgcu.edu# |
| Database link example: |
#c:\My Documents\database.mdb#MyTable |
- Description (optional) - Enter a brief description of
what the contents of the field are.
- Field Properties - Select any pertinent properties for
the field from the bottom pane.
Field Properties
Properties for each field are set from the bottom pane of the
Design View window.
- Field Size is used to set the number of characters needed
in a text or number field. The default field size for the text
type is 50 characters. If the records in the field will only have
two or three characters, you can change the size of the field
to save disk space or prevent entry errors by limiting the number
of characters allowed. Likewise, if the field will require more
than 50 characters, enter a number up to 255. The field size is
set in exact characters for Text type, but options are give for
numbers:
- Byte - Positive integers between 1 and 255
- Integer - Positive and negative integers between
-32,768 and 32,768
- Long Integer (default) - Larger positive and negative
integers between -2 billion and 2 billion.
- Single - Single-precision floating-point number
- Double - Double-precision floating-point number
- Decimal - Allows for Precision and Scale property
control
- Format conforms the data in the field to the same format
when it is entered into the datasheet. For text and memo fields,
this property has two parts that are separated by a semicolon.
The first part of the property is used to apply to the field and
the second applies to empty fields.
Text and memo format.
| Text Format |
| Format |
Datasheet Entry |
Display |
Explanation |
| @@@-@@@@ |
1234567 |
123-4567 |
@ indicates a required
character or space |
| @@@-@@@& |
123456 |
123-456 |
& indicates an optional
character or space |
| < |
HELLO |
hello |
< converts characters to lowercase |
| > |
hello |
HELLO |
> converts characters to uppercase |
| @\! |
Hello |
Hello! |
\ adds characters to the end |
| @;"No Data Entered" |
Hello |
Hello |
|
| @;"No Data Entered" |
(blank) |
No Data Entered |
|
Number format. Select one of the preset options from the
drop down menu or construct a custom format using symbols explained
below:
| Number Format |
| Format |
Datasheet Entry |
Display |
Explanation |
| ###,##0.00 |
123456.78 |
123,456.78 |
0 is a placeholder that displays a digit or
0 if there is none.
# is a placeholder that displays a digit or nothing if there
is none. |
| $###,##0.00 |
0 |
$0.00 |
| ###.00% |
.123 |
12.3% |
% multiplies the number by 100 and added a percent sign |
Currency format. This formatting consists of four parts
separated by semicolons:
format for positive numbers; format for negative numbers; format
for zero values; format for Null values.
| Currency Format |
| Format |
Explanation |
| $##0.00;($##0.00)[Red];$0.00;"none" |
Positive values will be normal currency format, negative
numbers will be red in parentheses, zero is entered for
zero values, and "none" will be written for Null
values. |
Date format. In the table below, the value "1/1/01"
is entered into the datasheet, and the following values are displayed
as a result of the different assigned formats.
| Date Format |
| Format |
Display |
Explanation |
| dddd","mmmm d","yyyy |
Monday, January 1, 2001 |
dddd, mmmm, and yyyy print the full day name, month name,
and year |
| ddd","mmm ". " d", '"yy |
Mon, Jan. 1, '01 |
ddd, mmm, and yy print the first three day letters, first
three month letters, and last two year digits |
| "Today is " dddd |
Today is Monday |
|
| h:n:s: AM/PM |
12:00:00 AM |
"n" is used for minutes to
avoid confusion with months |
Yes/No fields are displayed as check boxes by default on
the datasheet. To change the formatting of these fields, first
click the Lookup tab and change the Display Control to a text
box. Go back to the General tab choices to make formatting changes.
The formatting is designated in three sections separated by semicolons.
The first section does not contain anything but the semicolon
must be included. The second section specifies formatting for
Yes values and the third for No values.
| Yes/No Format |
| Format |
Explanation |
| ;"Yes"[green];"No"[red] |
Prints "Yes" in green or "No" in red |
- Default Value - There may be cases where the value of
a field will usually be the same for all records. In this case,
a changeable default value can be set to prevent typing the same
thing numerous times. Set the Default Value property.
Primary Key
Every record in a table must have a primary key that differentiates
it from every other record in the table. In some cases, it is only
necessary to designate an existing field as the primary key if you
are certain that every record in the table will have a different
value for that particular field. A social security number is an
example of a record whose values will only appear once in a database
table.
Designate the primary key field by right-clicking on the record
and selection Primary Key from the shortcut menu or select
Edit|Primary Key from the menu bar. The primary key field
will be noted with a key image to the left. To remove a primary
key, repeat one of these steps.
If none of the existing fields in the table will produce unique
values for every record, a separate field must be added. Access
will prompt you to create this type of field at the beginning of
the table the first time you save the table and a primary key field
has not been assigned. The field is named "ID" and the
data type is "autonumber". Since this extra field serves
no purpose to you as the user, the autonumber type automatically
updates whenever a record is added so there is no extra work on
your part. You may also choose to hide this column in the datasheet
as explained on a later page in this tutorial.
Indexes
Creating indexes allows Access to query and sort records faster.
To set an indexed field, select a field that is commonly searched
and change the Indexed property to Yes (Duplicates OK) if
multiple entries of the same data value are allowed or Yes (No
Duplicates) to prevent duplicates.
Field Validation Rules
Validation Rules specify requirements (change word) for the data
entered in the worksheet. A customized message can be displayed
to the user when data that violates the rule setting is entered.
Click the expression builder ("...") button at
the end of the Validation Rule box to write the validation rule.
Examples of field validation rules include <> 0 to
not allow zero values in the record, and ??? to only all
data strings three characters in length.
Input Masks
An input mask controls the value of a record and sets it in a
specific format. They are similar to the Format property, but instead
display the format on the datasheet before the data is entered.
For example, a telephone number field can formatted with an input
mask to accept ten digits that are automatically formatted as "(555)
123-4567". The blank field would look like (___) ___-____.
An an input mask to a field by following these steps:
- In design view, place the cursor in the field that the input
mask will be applied to.
- Click in the white space following Input Mask under the
General tab.
- Click the "..." button to use the wizard or
enter the mask, (@@@) @@@-@@@@, into the field provided. The following
symbols can be used to create an input mask from scratch:
| Input Mask Symbols |
| Symbol |
Explanation |
| A |
Letter or digit |
| 0 |
A digit 0 through 9 without a + or - sign and with blanks
displayed as zeros |
| 9 |
Same as 0 with blanks displayed as spaces |
| # |
Same as 9 with +/- signs |
| ? |
Letter |
| L |
Letter A through Z |
| C or & |
Character or space |
| < |
Convert letters to lower case |
| > |
Convert letters to upper case |
|
|
