Microsoft Access

Access 2013 Chapter 2 Using Design View, Data Validation, and Relationships Last Updated: 2/27/15 Page 1

USING MICROSOFT ACCESS 2013 Guided Project 2-2

Guided Project 2-2 San Diego Sailing Club wants to expand the database created in Guided Project 1-3 in Chapter 1. To ensure consistency, the starting file is provided for you. In this project, you use Design view to create a second table, edit field properties, and integrate data integrity rules. You also create a relationship between the two tables, enforce referential integrity constraints, and import data into the second table. Finally, you Compact & Repair the database.

Skills Covered in This Project  Create a table using Design view.

 Add fields into a table.

 Edit field properties in Design view.

 Create a validation rule and validation text.

 Save a table.

 Create a 1:M relationship between tables.

 Enforce referential integrity.

 Import records.

 Preview a table for printing.

 Compact & Repair a database.

IMPORTANT: Download the resource file needed for this project from the Resources link. Be sure

to extract the file after downloading the resources zipped folder. Please visit SIMnet Instant Help for

step-by-step instructions.

1. Open the SanDiegoSailing-02.accdb 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. Click the Table Design button [Create tab, Tables group] to open a new table in Design view.

5. Add new fields into the table and set field properties.

a. Type RentalID in the Field Name property box of the first row. Press the Tab key to move to the

Data Type property.

b. Select the Short Text Data Type. Press the Tab key to move to the Description property.

c. Type: Unique ID assigned to each rental

d. Click the Primary Key button [Table Tools Design tab, Tools group].

e. Press the Tab key to move to the next row.

f. Add the remaining fields into the table using the following information.

k. Click the Save button to save the table as SDRentals. l. Click in the Field Name property box of the RentalID field.

m. Type 5 in the Field Size property box in the Field Properties area.

n. Type Rental ID in the Caption property box.

Step 1

Download start file

g.

h.

i.

j.

Access 2013 Chapter 2 Using Design View, Data Validation, and Relationships Last Updated: 2/27/15 Page 2

USING MICROSOFT ACCESS 2013 Guided Project 2-2

o. Change the remaining field properties in the SDRentals table using the following information.

6. Save the table by clicking the Save button.

7. Create a field validation rule and validation text for the RentalDate field.

a. Click to select the RentalDate field.

b. Click in the Validation Rule property box.

c. Type > = #1/1/2015#.

d. Click in the Validation Text property box.

e. Type The Rental Date must be greater than or equal to 1/1/2015.

f. Save the table and switch to Datasheet view.

g. Type 1 in the RentalID field. Tab to the RentalDate field and type 7/5/2013.

h. Press the Tab key. The message box should display the contents of your Validation Text property.

Click OK. Type 7/5/2015 in the RentalDate field.

i. Press the Tab key. You should successfully move to the FourHourRental? field.

j. Click the Record Selector box to select this data record and click Delete [Home tab, Records

group] since you no longer need this sample data.

k. Click Yes to confirm deletion of this record.

l. Close the SDRentals table.

8. Create a one-to-many relationship between the SailboatFleet table and the SDRentals table.

a. Click the Relationships button [Database Tools tab, Relationships group].

b. In the Show Table dialog box, select the SailboatFleet table, and click the Add button.

c. Select the SDRentals table and click the Add button.

d. Click the Close button.

e. Drag the BoatID field from the

SailboatFleet table on top of the FKBoatID

field in the SDRentals table as shown in

Figure 2-83. Release the pointer. The Edit

Relationships dialog box displays.

f. Verify that the field names displayed are

correct. If necessary, use the drop-down

arrows to make any corrections.

g. Click the Enforce Referential Integrity

check box.

h. Click the Cascade Update Related Fields

check box.

i. Leave the Cascade Delete Related

Records check box not selected. The club does not want to delete rental records just because it

may take a boat out of service. This allows the club to keep a record of the rental.

j. Click Create. The Relationships window updates with the relationship line added. The 1 and infinity

symbols display, indicating that referential integrity is enforced.

k. Save the changes made to the relationships.

l. Click Close [Relationship Tools Design tab, Relationships group].

p.

q.

r.

s.

Access 2013 Chapter 2 Using Design View, Data Validation, and Relationships Last Updated: 2/27/15 Page 3

USING MICROSOFT ACCESS 2013 Guided Project 2-2

9. Import data from Excel into the SDRentals table.

a. Click the Excel button [External Data tab, Import & Link group] to launch the Get External Data –

Excel Spreadsheet dialog box.

b. Click the Browse button to launch the File Open dialog box.

c. Locate and select the SDRentalsDataFile-02.xlsx Excel file. (Downloaded from the Resources link.)

d. Click the Open button. The File Open dialog box closes.

e. Select the Append a copy of the records to the table radio button.

f. Click the drop-down arrow in the Table Name box and select the SDRentals table.

g. Click OK to launch the Import Spreadsheet Wizard. The data records should display in the Wizard

window.

h. Click the Next button to advance to the next page of the Import Spreadsheet Wizard.

i. Verify that the Import to Table: box contains the SDRentals table.

j. Click Finish.

k. Click the Close button.

10. Open the SDRentals table in

Datasheet view. Your table should

have the six records shown in

Figure 2-84.

11. Edit field properties in the SDRentals

table.

a. Click the Design View icon

(Figure 2-85) in the Status bar to

switch to Design view.

b. Change the Required property for all fields except FourHourRental? to Yes.

c. Save the changes to your table. Access displays a

message box warning you that data integrity rules

have changed. Click Yes to test your data against

the new rules.

d. Close the SDRentals table.

12. View the related data in the SailboatFleet table.

a. Open the SailboatFleet table in Datasheet view.

b. Click the plus sign to the left of the BoatID field for BoatID 1010. You should see two rentals for

this boat.

c. Close the SailboatFleet table.

13. Preview the data records in the SailboatFleet table for printing.

a. Select the SailboatFleet table in the Navigation pane.

b. Click the Print button [File tab] to display the printing options.

c. Click the Print Preview button. The Print Preview tab opens and shows a preview of how the table

will print.

d. Click the Landscape button [Print Preview tab, Page Layout group] to change the page

orientation.

e. Click the Close Print Preview button.

14. Use Compact & Repair to reclaim unused space.

a. Click the File tab. The Info button should be selected.

b. Click the Compact & Repair Database button.

15. Close the database.

16. Upload and save your file.

17. Submit project for grading.

Step 2

Upload & Save

Step 3

Grade my Project

Download Resources