Creating a Table in Access
Methods for Creating Tables
Having selected "Tables" on the left of the database window, you can create a table
by double clicking any of the three items shown i.e. Create table in design view,
Create table by using wizard and Create table by entering data. These three options
and more are available by clicking the "New" button at the top of the database window.
The New Table dialog box offers extra options to import an existing table from another database or create a link to a table in another database.
The "Datasheet View" option in the New Table dialog box or double clicking "Create table by entering data" in the database window both act to open a table with ten columns and 21 rows into which you can start entering data.
Whilst this method may be handy if you are in a hurry, you will still need to go into design view and tidy the table up at a later stage.
The Table wizard allows you to select the fields you want from a range of business
or personal sample tables. Select the tables most similar to the one you are trying
to create and place the fields in your own table, renaming them if desired. The
buttons show a single chevron (> or <) to select or deselect a field. The
double chevron (>> or <<) is used to select or deselect all the displayed
fields.
The snag with using the wizard is that the fields tend to have an American bias
with fields for items such as SocialSecurityNumber and PostalCode being geared up
for their American equivalents rather than UK values.
The best method for creating tables is to use design view as shown in the animation
above. The data types available in design view are as follows:
Data Types
- Text is used for text (or numbers that will not be used for calculation
such as telephone numbers). Up to 255 characters may be included in a field.
- Memo is used for larger amounts of text and numbers (up to 65,535
characters).
- Number is used for numeric data only (see number types).
- Date/Time is used for date and time values for the years 100 to
9999.
- Currency is a special number type accurate to 15 digits to the
left of the decimal point and 4 to the right.
- AutoNumber provides a unique number for each record starting at
1 and incrementing by 1 at a time. It is often used for ID fields in promary keys
and where numbers have no intrinsic value apart from their uniqueness e.g. invoice
numbers.
- Yes/No is used for Yes/No, True/False or On/Off options and is
usually displayed as a chexck box.
- OLE Object is used to include objects such as music files, spreadsheets,
graphics and so on. It is often used for items such as photos in staff records or
catalogues.
- Hyperlink is used to store hyperlinks.
- Lookup Wizard can be used to display values from another table
in a drop down list for easy selection.
When in design view, the field properties for the currently selected field can be
set in the lower part of the design view window. Some typical examples are shown
below:
Field Properties
- Field size sets the maximum number of characters in a text field
or the size of a number (see number types).
- Format offers a choice of numeric and date formats. If Long Date
is chosen for a date field, the date will be shown in long format regarless of how
it was entered (illustrated in the demonstration at the top of the page).
- Caption is the text used to describe to field in forms, reports
etc. It is good practice to give fields one word names such as "DateOfBirth" but
use "Date of Birth" as the caption for display purposes. If there is no Caption, Access uses the field name.
- Default Value is used to give the field a value if none is entered
by the user.
- Required determines whether or not an entry must be made in the
field.
- Indexed offers three choices: No, Yes (Duplicates OK) and Yes (No
Duplicates). Indexing fields helps speed up sorting but should not be used if you
will never want to sort the field.
Number Types
- Byte is for whole numbers between 0 and 255
- Decimal is used for decimal numbers in the range -10E28-1 and 10E28-1
(if you are not into scientific notation the E28 stands for 28 zeroes).
- Integer is used for whole numbers from -32,768 to 32,767.
- Long Integer is used for whole numbers from -2,147,483,648 and
2,147,483,647.
- Single is used for numbers from -3.402823E38 to -1.401298E-45 for
negative values and from 1.401298E-45 to 3.402823E38 for positive values.
- Double is used for numbers from -1.79769313486231E308 to -4.94065645841247E-324
for negative numbers and from 4.94065645841247E-324 to 1.79769313486231E308 for
positive values.
- AutoNumbers are long integers.
When in design view, the toolbar buttons here below may be of use:
From left to right the buttons are:
- Primary Key used to set or unset the primary key.
- Indexes displays a dialog box showing the indexed fields in the
table (see picture below).
- Insert Rows inserts a row above the currently selected row.
- Delete Rows deletes the current row (alternatively, select the
field header button and press the delete key).
- Properties shows table properties (beyond the scope of this section
of notes).
- Build starts a wizard to create a new field (see picture below).
You can always toggle between design view and datasheet view using the leftmost
toolbar button:
Top
or
Take a short test
or
Download notes