Improve It Project 2-7

Placer Hills Real Estate expanded the database created in the Improve It Project 1-7 in Chapter 1. To ensure consistency, the starting file is provided for you. For this project, you use Design view to edit properties in a table and integrate data integrity rules. You edit data records, create a relationship between the two tables, and enforce referential integrity constraints. Finally, you view how the table records will print using Print Preview. This project has been modified for use in SIMnet.

Skills Covered in This Project -

• Edit a table using Design view.
• Edit field properties in Design view.
• Create an input mask.
• Save a table.
• Create a foreign key.
• Create a 1:M relationship between tables.
• Enforce referential integrity.
• View a table in Datasheet view and expand related records.
• Preview table results for printing.

1. Open the PlacerHills-02 database start file.

2. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor.

3. Enable content in the security warning.

4. Open the Agents table in Datasheet view (Figure 2-95). 

a. Look at the DateHired and Phone fields. The manager of the real estate office wants those to be formatted differently.

b. Notice that both Jack Johnstone and Kallyn Duarte have the same value in the EmployeeNum field. If the employee number is supposed to be unique, something must not be set correctly with the primary key in this table.

c. Change Kallyn Duarte's EmployeeNum to be 168, the correct value.

5. Change to Design view to make needed corrections to the Agents table.

a. Notice that the table does not have a primary key. Although Access recommends that you assign a primary key in a table, Access lets you create a table without one.

b. Set the EmployeeNum as the primary key.

c. Set the Format property of the DateHired field to use Short Date.

d. Create an input mask for the Phone field. Select the Phone Number Input Mask. Don't make any changes to the mask or placeholder character and select the Without the symbols in the mask, like this radio button. Complete the Input Mask Wizard.

e. Change all fields to be Required.

f. Save the table.

g. Switch to Datasheet view to ensure that the formatting shows as desired.

h. Adjust the field width of the DateHired field to 13. Remember that you can select the column, right-click to open the context menu and then select Field Width to change the width to a specific size.

i. Adjust the field width of the Phone field to 16.

j. Save and close the Agents table.

6. Determine the type of relationship and the foreign key field.

a. There is a one-to-many relationship between the two tables since an agent can list many different real estate properties, but a property is listed by only one agent.

b. The foreign key comes by taking the primary key field from the 1 table and adding it in as an additional field in the M table. In this case, the EmployeeNum should also be added into the Listings table.

c. Open the Listings table in Datasheet view. Notice that this table has a field that stores the agent's name. However, it doesn't have the foreign key. To minimize redundant data, you will change the Agent field so that it will now store the agent's employee number instead of the agent's name. This requires changing the data values as well as field properties.

7. Edit the data values in the Agent field in the Listings table.

a. Click the arrow to the right of the Agent Field Name and select Sort A to Z. Because the employee name values begin with the first name, the sort isn't alphabetical by last name. However, the sort makes it easier to change the data values since all the property listings by the same agent are grouped together.

b. Type 169 in the Agent field for Gerardo Montoya.

c. Change each of the six records for Jack Johnstone to have the value of 160 for the Agent field. After entering the first value, you can use copy and paste to enter the remaining values.

d. Continue changing the remaining records using the information in the following table: 

j. Remove the sort from the Agent field.

k. Save the table.

8. Switch to Design view in the Listings table to edit the properties of the foreign key.

a. Select the ListingAgent field.

b. Change the field name to ListingAgentNumber. In this database, Placer Hills prefers that the foreign key have a unique name instead of choosing to use FKEmployeeNum as the name of the field.

c. Change the Description to read Must match an EmployeeNum in the Agents table.

d. Change the field size to 3.

e. Save and close the table. Access warns you that the field size has been changed and that you may lose data. Your values meet this new size. Click Yes.

9. Create a one-to-many relationship between the Agents table and the Listings table.

a. Open the Relationships window and add the two tables.

b. Enlarge the table objects, if needed, to see all of the fields.

c. Drag the EmployeeNum field from the Agents table on top of the ListingAgentNumber field in the Listings table as shown in Figure 2-96.

d. Select the correct choices to Enforce Referential Integrity and Cascade Update Related Fields. 

e. Leave the Cascade Delete Related Records check box not selected. You do not want to delete property listing records just because an agent may leave the company. This allows you to keep a record of the listings.

f. Create the relationship.

g. Save the changes and close the Relationships window.

10. Open the Agents table in Datasheet view.

a. Click to expand the records for Kelly Weatherby. There should be nine properties listed.

b. Close the Agents table.

11. Preview the data records in the Listings table for printing.

a. Select the Listings table in the Navigation Pane.

b. Use Print Preview to show a preview of how the table will print.

c. Change the page to Landscape orientation.

d. Close the Print Preview.

12. Close the database.

13. Upload and save your project file.

14. Submit project for grading.

Attachment:- Assignment File.rar