On another line, you see a name as Gwen Simms. You ask the manager whether it is the same or another customer. The manager says it is the same customer. On another piece of paper, you see the name Gwenn Simm. After asking, the manager confirms once again that it is the same customer, just typed differently. A computer database helps to solve this type of problem so that information can be typed once, in one list. Then, the information in that list can be made available to other lists that can use it by selecting it, instead of re-typing it.
So far in our databases, we created a table with the necessary columns. To perform data entry, we were typing the necessary value in each field. If we needed another table, we would just create one. This made it possible for a database to have as many tables as we wanted, except that these lists did not communicate. There was a risk of error if we tried to enter the same information in various lists.
One of the rules a database developer should follow is to avoid duplicate entry of data. That is, the same piece of information should not be entered in two different lists. For example, when a potential customer wants to rent a room at the hotel, you can create an account that the clerk can use to enter the customer's information in one table. This information can consist of the name, the phone number, the emergency contact (name and phone number), etc:
In the same way, you would need a list of the rooms available for renting to customers. Each room would hold some information such as the type of room (regular bedroom, studio, or conference room, etc), the type of bed (queen, double, or king), the rate, and the availability. We cannot put the room information in the customer's information because the room does not belong to the customer and, even if a particular customer A is renting a room 104 today, that room would be rented to another customer next time. If we put the room information in a customer's information, when another customer comes, we would have to re-enter the same room information into the other customer's record. This is not professional and is prone to error. Therefore, the customer and room's information should be kept separate.
After creating the customer information and the rooms lists, to rent a room, we can select the customer and associate him or her to a room:
One of the problems we need to solve is to keep track of the customer's room occupancy: what night the customer stays in the allocated room and how much is charged for the room for one night or for each night the customer uses it. The manager might also have indicated that the hotel offers wireless Internet access but the customer has to pay a one-time fee for it if he or she wants it. The customers are also allowed to use the phone in their room but they must pay for each phone call placed outside the hotel. Therefore, simply assigning the room to a customer does not take care of tracking the regular charges and expenses. To solve this problem, we can create another list in which we would enter some information for the customer (such as his or her name or an account number) and some room information (such as the room number):
This is the idea behind a relational database. A relational database is an application that contains two or more tables so that information in one table is made available to another table or other tables that need(s) it. The information is entered once in one particular table. If the same information is needed in another table, it is simply identified one way or another. This reduces, and can eliminate, the likelihood of mistakes that result from duplicate data.
As its name implies, to create a relational database, you must have a way for tables to communicate or relate to each other. To start, for a table to make its information available to the other lists, the table must have a way to be identified. This is done by creating a field used to refer to that table. This field is called a primary key. The primary key can be represented by one field or it can be a combination of fields.
To make a field a primary key, display its table in the Design View. You have two options:
The field that is made the primary key would then appear with a key icon to its left. When you click a field that is a primary key, the Primary Key button becomes highlighted .
To create a primary key of more than one field, display the table in Design View. Select, as a group, the fields that would constitute the primary key. Then:
The fields would then appear each with a key icon to its left:
One of the rules that the primary key must follow is that it must be able to uniquely identify each record in the table. If you make a field a primary key, you can instruct the person performing data entry to make sure no two records have the same value for that field. Sometimes this can be easy to implement. For example in a small company of 2 to 20 people, it is usually easy to make sure that each employee is assigned a unique number. In a database with many records such as a department store that gives credit cards to its customer, it can be difficult to give a unique account number to each customer. In fact in this case, the clerk performing data entry might not have the appropriate number for a customer when creating his or her account. Fortunately, Microsoft Access (like most database environments) provides a quick fix to this.
To automatically have a unique identifier associated with each new record created on a table, you can create a field whose data type is AutoNumber. When a field receives this data type and when the clerk creates the first record, it receives the number 1. Every time a new record is created, the number is increased and assigned to the field. The number never repeats. If a record is deleted, the numbers are not reset: the deleted record is gone with its assigned unique number. This ensures that each record keeps a unique number.
The AutoNumber in Microsoft Access is not a real data type, just as, except for Text, none of the items in the Data Type combo box of the Design View of the table is a true data type. Their names are only made friendly to help you identify their types. AutoNumber is actually a long integer.
We have seen how to create a primary key to prepare a table for a relationship. The primary key makes it possible for such a table to make its data available. It only signals this to the other table(s) of the (same) database. If one table wants to use the data stored in another table, the first table has to be prepared for it. The first table can be called the parent table. The other table can be called the child table.
To make it possible for the child table to access the data in the parent table, the child table must have a field that would communicate with the parent table. This field represents the parent table. To act as a liaison between both tables, the field in the child table communicates with the primary key of the parent table. In order to get this communication to work, the communicating key in the child table must use the same data type as the primary key of the parent table. Since the field in the new table is only used to represent the data from the first table, it is called a foreign key:
In the same way, any table that wants to use data from a certain table must have a foreign key that can communicate with the primary key of that parent table.
To make it easy to identify a foreign key in a table, it is a good idea, not a requirement, to give to the foreign key the same name as that of the primary key. The only real requirement is that both fields use the same data type. Remember that AutoNumber is not an actual data type. Therefore, if the primary key is of type AutoNumber, the foreign key should use the Long Integer as its data type after selecting the Number in the Data Type combo box of the Design View of the table.
TThere are various ways you can create and manage a relationship between two tables. Once you have a primary key in one table, you can ask Microsoft Access to create and even configure a foreign key for you. You can simply indicate where the data will come from; that is, you must indicate the table that holds the primary key, select the field that holds the actual data to use. Microsoft Access would take care of configuring everything, or almost everything, behind the scenes for you. For this approach, you use a lookup field, which can be a combo box or a list box.
As done with the simple lookup, you can create a field whose data would be selected from a list. As opposed to an unbound lookup field whose values you can predict at the time you are creating a database, a bound lookup field is one whose values are not known in advance. The values for such a field become available as the database is growing./p>
To create a bound lookup field, you can open the table in Design View, set the data type of the field to Lookup Wizard... This would open the Lookup Wizard. Since you are creating a field that would get its data from another table or query, you must select the first radio button and click Next. Then follow the wizard.
You can also create a lookup combo box using the Datasheet View of a table. To do this:
Any oh these actions would open the Lookup Wizard. Since you are creating a field that would get its data from another table or query, you must select the first radio button and click Next. Then follow the wizard.
When a column is a lookup field, if you don't like the way it behaves, you can reconfigure it. To do this:
When creating a bound lookup field, if you select only one column in the third page of the Lookup Wizard, a combo box would be created so the user can select the desired value. If the value you selected represents some type of insignificant number or character, when the user clicks the arrow of the combo box, the list of values that appear can be confusing and could lead the user to select the wrong one. Consider the following example:
When the user clicks the arrow of the combo box to select a room, this list does not specify what type of room the number represents. Any number could be for a bedroom, a studio, or a conference room. Because these numbers are vague, the user could select the wrong number and for example assign a conference room to a person who wants to rent a simple bedroom. There are various ways you can solve this type of problem. If no records have already been created and that involve that field, you can recreate the lookup field and make it display more than one column of values.
If you are creating or recreating a lookup field and you want it to display more than one column, in the third page of the wizard, you can double-click each of the desired values from the Available Fields list box:
Then continue with the wizard. When you finish with the wizard, Microsoft Access would take care of configuring the column. Sometimes you will not like the way Microsoft Access did the job. You can then modify it to your liking.
If the records exist already, you can simply modify the configuration of the lookup field. You have many options.
The configurations of the lookup field can be found in the Lookup section of the bottom part of the table in Design View. To configure a lookup field, click it in the top section of the table and, in the bottom section, click the Lookup tab. Here is an example:
To change a property, click it in the left section and change the value in the right section: