Get documents about "Microsoft Access Tables Relationships
Document Sample
Microsoft Access: Tables & Relationships
This workshop requires completion of "Access: Basics". Topics include importing and
linking tables, data types, formats, input masks, data entry, table analyzer, lookup values,
copying tables, primary keys and multiple primary keys, relationships and AutoForms.
Worksheets: Access Tables, Access Relationships
Topics: - Relationship Presentation
- Junction Tables
- Primary Keys
- Foreign Keys
- Creating a New Database
- Linking a table
- Changing Data in Both Sources (Access & Excel)
- Deleting a table
- Importing a table
- Design View
- Field Types, Sizes and Formats
- Input Masks vs Formats
- Sorting Fields
- Table Analyzer
- Look-up Values
- Relationships
- Enforcing referential integrity
- Copying Tables
- Multiple Primary Keys
- AutoForm
Microsoft Access – Tables
Page 1
There are several ways to create a new table:
entering data (Datasheet View), creating the
fields (Design View), using a Table Wizard,
Importing a Table and Linking a Table.
Importing and Linking are used when you have a
data table already created in another file, such as
Access, Excel, comma and delimitated text files,
or any other data file that is organized and ready
to import or link. Either option will take you
through a wizard to set up your data.
Linking will place a copy of the table into your database but it will still be connected to the
original source of the data. If the original is changed it will change the table in Access; if the
table is changed in Access, it will change the original. You will not be able to change many of
the options of a linked table (such as field size or setting primary keys) because Access cannot
enforce these rules on data outside of the program. If you need to change these options you
should Import instead of Link. Importing a table will place a copy of the data as a Table in
your database, entirely separate from the original.
Create Table by Table Wizard
The Table Wizard has several preset lists that allow you to choose fields for your table, based
on Microsoft Access sample tables.
In this wizard, you can choose
which type of table, Business
(such as Contacts) or Personal
(such as Recipes). Then you
can choose the individual
fields you wish to use. Select
a sample field from the list
and use the button to
move the selected field to the
Fields in my new table: list.
The double arrow will move
all the fields to the right. The
arrows going in the opposite
direction allow you to remove
selected fields from the list.
Sometimes it is easier to move all the fields to the right and then move the few fields you don't
want back to the original Sample Fields:.
Once you have set up the fields, click the Next > button.
Microsoft Access – Tables
Page 2
This next screen allows you to
choose a name for your table
and decide if the wizard will
set the primary key or if you
will set one at a later time (or
possibly not at all).
As it says in this window, the
primary key is the special
field that uniquely identifies
each record within your table.
A primary key is not required,
but it is a good idea to
maintain the uniqueness of
each record.
Click Next > to continue.
As this third screen says, this
is all the information that
Access needs to create the
table, only one choice remains
to be made, what do you want
to do with this table when
Access finishes creating it?
Your choices are to Modify
the table design (view the
design view of this table),
Enter data directly into the
table (see the datasheet view
of this table) or Enter data
into the table using a form
the wizard creates for me
(create the table and a data entry form).
If you check the help box, Access will launch the office assistant to help you through setting up
the rest of your table.
Microsoft Access – Tables
Page 3
Create Table by Entering Data
When you choose this option, Access opens the table in the datasheet view:
Access gives you 20 blank records and 10 blank fields. To change the name of the fields, double
click on the words in the field title (Field1). This will highlight the words and allow you to enter
field name you desire. Type the name you want and press Enter.
Once you have the field names set up, you can enter data into the table.
When you change to Design view, Access will ask you to save the table. In the Design view you
will see the fields you created and Access will set data types based on the data you entered. On
this screen you can add new field names, change the data types and set a Primary Key.
When you return to the datasheet view you will see only the fields and records that you created.
Microsoft Access – Tables
Page 4
Create Table in Design View
When you choose this method to create a table you will see a blank design view.
Notice at the bottom of the screen, the blue text tells you what you are filling in. In this case, the
field name, Access is telling you that the name can be up to 64 characters long, counting the
spaces. It also tells us that F1 will bring up the help file for field names.
The help feature can actually be a very useful resource in Access. Below is an excerpt from the
help file. It is a set of object-naming rules that apply to the field name, as well as a few tips to be
aware of.
Object-naming rules
A set of specific rules for naming Microsoft Access objects. In Microsoft Access, names can be
up to 64 characters long and can include any combination of letters, numbers, spaces, and special
characters, except a period (.), an exclamation point (!), an accent grave ('), and brackets([ ]). Note
that you also can't use leading spaces or control characters (ASCII values 0 to 31). For
information on Visual Basic naming conventions, search the Help index for "naming
conventions."
Tips
• Avoid including spaces in object names if you'll frequently refer to the objects in expressions
or Visual basic code.
• Avoid using extremely long names because they are difficult to remember and refer to.
Once you have typed your field a name, you can choose a data type. By default
the fields will be text. Here is a list of the different data types. Each will have
different field properties that will appear across the bottom of the screen. There
is more about each data type on the next worksheet.
Microsoft Access – Tables
Page 5
These are the general properties for a text field. Access provides details for each of these
properties in blue text on the on the right side of the window.
FIELD TYPES
Text - Text can contain any alphanumeric characters (letters, numbers, symbols, punctuation).
The maximum field size (the number of characters including spaces) is 255, and the
minimum is 1. Access will hold the data to this limitation, for example for a State field
you may limit the field size to 2, so that the user will only enter the abbreviations, "FL"
instead of "Florida". There are only a few formats that can be used for text fields, ones
that you may find useful are: < formats all characters to lowercase, and > formats all
characters to uppercase. Input masks have to be text or date fields. In the Format or
Input Mask you can use L for letters, 0 for optional numbers, 9 for required numbers,
and A for numbers or letters.
Memo - This type of field is very similar to the Text field, except it has no field length property.
The memo field takes up only the amount of spaced used per record. A memo field is
limited to 65,535 characters, including spaces. You can also use the < or > signs to
control the capitalization of the characters.
Number - A Number field is limited to only numeric input (numbers, decimal points, +/- signs).
This is a field that can be used for mathematical purposes. There are several field
sizes available for number fields - shown here on the left (see the next page for more
information). There are several options for the format as well, shown here on the
below. You can also set up your own format using zeros.
Currency - This field type is a specialized Numeric type, which always has the $ format.
Microsoft Access – Tables
Page 6
Number Field Size: (From Access 2000 Help File)
Storage
Setting Description
Size
Byte Stores numbers from 0 to 255 (no fractions). 1 byte
Decimal Stores numbers from -10^38 -1 through 10^38 -1 (.adp) 12 bytes
Stores numbers from-10^28 -1 through 10^28 -1 (.mdb)
Integer Stores numbers from –32,768 to 32,767 (no fractions). 2 bytes
Long (Default) Stores numbers from –2,147,483,648 to 2,147,483,647 (no fractions). 4 bytes
Integer
Single Stores numbers from 4 bytes
–3.402823E38 to–1.401298E–45 for negative values and from 1.401298E–45 to
3.402823E38 for positive values.
Double Stores numbers from 8 bytes
–1.79769313486231E308 to –4.94065645841247E–324 for negative values and from
1.79769313486231E308 to 4.94065645841247E–324 for positive values.
Date/Time - Date/Time allows you to enter Dates and/or
Times. Access will store these dates and times similarly to
Excel, such that you will be able to add and subtract from
these dates. The preset formats for the date are shown on the
left. If you wish to make your own date format use the table
below as a guide.
Dates for Friday, February 1, 2002
Day Month Year
d 1 m 2 y 02
dd 01 mm 02 yy 02
ddd Fri mmm Feb yyy 2002
dddd Friday mmmm February yyyy 2002
To get the format, “Friday, February 1, 2002” you would choose the Long Date or type in “dddd,
mmmm d, yyyy”. In time formats you can use the H for hours, M for minutes and S for seconds.
AutoNumber - This field is most often used as a primary key, because it places a unique
sequential number for each record. If a record is deleted, the table is not
renumbered. You can add a format to this using letters and zeros.
AutoNum Name AutoNum Name AutoNum Name
1 Jack 1 Jack 1 Jack
2 Jill 2 Jill 3 John
3 John 3 John
Microsoft Access – Tables
Page 7
Yes/No - This is a binary field, its format can be set to Yes/No, True/False, or On/Off. In each
of these cases, there are only two options. This will usually show up in your table as
a check box. If the box is checked, then the value is Yes, True or On, if the box is not
checked then the value is No, False, or Off.
OLE Object - OLE stands for Object Linked or Embedded. This is the option you would use if
you needed to embed a file to this record, for example: a picture of employee to
that employee record. When you reach the OLE field, go to the Insert Menu, and
choose Object or right click and choose Insert Object. If you wish to Link instead
of Embed a file, be sure to click on the Link checkbox. You will only see the file
type when looking at the datasheet view (tables/queries). You should be able to
see the actual contents of the file on forms and reports.
Hyperlink - A hyperlink data type would be used for email addresses or web pages. Access
makes any information in these fields active links, including the blue and
underlined format we associate with hyperlinks.
Lookup Wizard - The lookup wizard allows you to link the field to another table, in order to set
up a drop down arrow to choose your data from the other table or query. This
will automatically link the two tables (but will not enforce referential
integrity). When using this field, be sure to check the Look Up Properties at
the bottom of the window.
MS Access – Relationships
(from Access Online Help)
About relationships in a database
After you've set up different tables for each subject in your database, you need a way of telling
Microsoft Access how to bring that information back together again. The first step in this process is to
define relationships between your tables. After you've done that, you can create queries, forms, and
reports to display information from several tables at once. For example, this form includes information
from five tables:
How do relationships work?
In the previous example, the fields in five tables must be coordinated so that they show information
about the same order. This coordination is accomplished with relationships between tables. A
relationship works by matching data in key fields, usually a field with the same name in both tables. In
most cases, these matching fields are the primary key from one table, which provides a unique identifier
for each record, and a foreign key in the other table. For example,
employees can be associated with orders they're responsible for
by creating a relationship between the Employees table and the
Orders table using the EmployeeID fields.
MS Access – Relationships
(from Access Online Help)
DEFINITIONS
Relationship: An association established between common fields (columns) in two tables. A
relationship can be one-to-one, one-to-many, or many-to many.
Primary Key: One or more fields whose value or values uniquely identify each record in a table. In a
relationship, a primary key is used to refer to specific records in one table from another table. A
primary key is called a foreign key when it is referred to from another table.
Foreign Key: One or more table fields to the primary key field or fields in another table. A foreign
key indicates how the tables are related – the data in the foreign key and primary key fields must match.
A one-to-many relationship
A one-to-many relationship is the most
common type of relationship. In a one-
to-many relationship, a record in Table
A can have many matching records in
Table B, but a record in Table B has
only one matching record in Table A.
A many-to-many relationship
In a many-to-many relationship, a record in Table A
can have many matching records in Table B, and a
record in Table B can have many matching records
in Table A. This type of relationship is only possible
by defining a third table (called a junction table)
whose primary key consists of two fields, the
foreign keys from both Tables A and B. A many-to-
many relationship is really two one-to-many
relationships with a third table. For example, the
Orders table and the Products table have a many-to-
many relationship that's defined by creating two
one-to-many relationships to the Order Details table.
MS Access – Relationships
(from Access Online Help)
A one-to-one relationship
In a one-to-one relationship, each record in Table A can
have only one matching record in Table B, and each
record in Table B can have only one matching record in
Table A. This type of relationship is not common,
because most information related in this way would be in
one table. You might use a one-to-one relationship to
divide a table with many fields, to isolate part of a table
for security reasons, or to store information that applies
only to a subset of the main table. For example, you
might want to create a table to track employees
participating in a fundraising soccer game.
Defining relationships
You define a relationship by adding the tables you want to relate to the Relationships window, and then
dragging the key field from one table and dropping it on the key field in the other table.
The kind of relationship that Microsoft Access creates
depends on how the related fields are defined:
A one-to-many relationship is created if only one of
the related fields is a primary key or has a unique
index.
A one-to-one relationship is created if both of the
related fields are primary keys or have unique
indexes.
A many-to-many relationship is really two one-to-
many relationships with a third table whose primary
key consists of two fields – the foreign keys from the
two other tables.
Note If you drag a field that isn't a primary key and doesn't have a unique index to another field that
isn't a primary key and doesn't have a unique index, an indeterminate relationship is created. In queries
containing tables with an indeterminate relationship, Microsoft Access displays a default join line
between the tables, but referential integrity won't be enforced, and there's no guarantee that records are
unique in either table.
Related docs
