A single table in a database is a lonely and weak thing. Several related tables can be much more efficient and powerful.
By creating multiple tables and establishing relationships between the tables, you can drastically increase the usefulness of the database. It makes it easier to store more information and to answer more complex questions.
Examples below are from starwars.accdb and languageartsclasses.accdb
.
Tables are related when they share a field. We can also say that the tables are joined. It reduces confusion to use exactly the same field name in both tables, though that is not absolutely required.
In the illustration below, there are four tables. Three tables are related to the same table, Star Wars Collectibles. The shared fields are EpisodeID, CategoryID, and SupplierID, which are the primary key fields in their own tables. These are called foreign keys for the table Star Wars Collectibles.
Even with just a few related tables, you can save yourself a lot of typing and a lot of confusion.
Saves repeating data: Without a table Suppliers, each record in the main table, Star Wars Collectibles, would include all of the supplier's information. So much extra typing! With a related Suppliers table, you only need to enter the supplier's ID number in the record.
Avoids typing errors/inconsistencies: Different versions of a value will sort and filter differently. Can you get it right every time? "1 - The Phantom Menace 1999" is not the same as "The Phantom Menace" or, when your fingers get uncooperative, "Teh Phantom Memace"! With a related Episodes table all of that is decided once. Then you just have to get the episode's ID number into the field in the main table. The easiest way is a Lookup list.
Lookup lists: A Lookup field lets you select information from a list. This guarantees that there are no typing errors and that you use the same values throughout the database! (More on this below)
The more records you have and the more information that would be duplicated in each one, the more attractive a relational database becomes.
There are three kinds of relationships between tables.
The most common type of relationship.
A record in the first table can match many records in the second table, but a
record in the second table can match only one record in the first table.
One Episode can match many items in Star Wars Collectibles but
each item is from a particular episode.
(The table includes "not in movies"
for Star Wars items that are not actually in a movie episode.)
The Relationships window only shows the 1 and the infinity symbol ∞ when the relationship is defined to Enforce Referential Integrity. This fancy phrase means that Access will not let you use a value on the Many side that does not already exist on the One side.
Example: In the Star Wars Collectibles database, the Episodes table has a One-to-Many relationship to the Star Wars Collectibles table. Referential integrity means that you cannot enter 7 as EpisodeID in a record in the Star Wars Collectibles database because the largest EpisodeID in the Episodes table is 6. You would FIRST have to create a record with EpisodeID=7 in the Episodes table, THEN you can use that value in the Star Wars Collectibles table.
A Many-to-Many relationship requires creating a third table, called a junction table or link table, to
join the records from the other two tables.
Example: In a classroom database, each assignment is done by many students. Each student has many assignments. The Assignments and Students tables can be related only through a third table, Scores. Each record in the Scores table contains the score for one assignment completed by one student.
So the Many-to-Many relationship is divided into two One-to-Many relationships.
Not common. Each record in the first table matches only one
record in the related table and each record in the second table matches only
one record in the first table.
Why not just put all of the information into one table?
When there are many, many fields, splitting a table can
make working with the records easier.
You might split the table to put sensitive information in a
table that is in a linked database that has a password, for security.
You could use a duplicate or partial duplicate table for a special event or
purpose.
Example: In the Language Arts Classes database, Six Weeks Grades table holds the term grades for the year while the table Students holds personal information about students. These tables have a one-to-one relationship. Having separate tables keeps down the number of fields in each one. This makes it easier to work with both tables.
It is always easier to pick from a list than to remember what the choices are! In the bottom section of the Table Design View, each field has a Lookup tab. For some data types, like AutoNumber and Memo or Long Text, the tab is blank because that data type cannot use a Lookup list.
The easiest way to create a Lookup list is to use the Lookup Wizard.
Click to
see the wizard steps:
Step 1 > Step 2 > Step 3 > Step 4 > Step 5 > Step 6
When you create a Lookup list for a field, the list is available to you in a both the datasheet and form views. In a report Access leaves space in a field for the drop arrow but you don't normally see it and you cannot use it.
Examples of Lookup Lists | |
---|---|
Datasheet: | Form: |
|
Warning: Deleting a Lookup Field
A Lookup list uses a relationship between your table and whatever you used to populate the list. To delete a field that uses a Lookup list, you have to remove the relationship first. You may need to Show All in the Relationships window to see the relationship created by the Lookup Wizard.
The properties of the Lookup list are on the field's Lookup tab. You can set these properties yourself without going through the wizard, once you know what they are all about.
Display
Control:
Text Box, List Box, or Combo Box
A Text Box does not offer a list at all. There are no Lookup properties if you choose this type.
A List Box will accept only items from the Row Source.
A Combo Box can show several fields, like Firstname, Middlename, and Lastname. This type has more properties on the Lookup tab than a List Box.
Row Source Type = Value List, Table/Query, or Field List:
Value List:The illustration shows that the Row Source Type for the field Condition is a Value List, written out in the Row Source property itself.
You enter the items for the list in the Row Source yourself. Note the punctuation that is
required! Double quotes around the value and semicolons between values.
Combo Box:
The second illustration shows that the field EpisodeID uses a combo box to display values from a query that is saved as part of
the definition of this table. The row source could have been another table
or a query that is saved outside the table.
Field List: Pick a table or query and pick from a list of the fields in it. This is not often used. It could be used, for example, in order to create a form where the user picks a field to sort on.
Bound Column: The data in the bound column of the row source is what will actually be stored in this field in this table. Often you are storing the AutoNumber primary key for a record in a different table.
Column Count: The number of columns from the row source to display in the drop list. (You might expect this property to come before Bound Column!)
Column Heads: If it is not obvious what the values in the columns mean, you can show the field names as column heads in the combo list.
Column Widths: You can set how much space to allow for each column in the drop list. You can save space in the list's display if you know what the widest item is.
List Rows: The number of rows from the row source to show at one time. A vertical scrollbar will appear if there are more items than can be shown.
List Width: Usually this is the same as the sum of the column widths.
The remaining properties involve letting or preventing users from adding new items to the drop list. It is usually a bad idea to let users add items! When you do need to allow this, you will need to provide quick access to a form to update the source of the list and then refresh it. This is not something we will be doing in these lessons.