Access 2024 In Practice - Ch 2 Guided Project 2-1
SIMnet Access project instructions
· Excel Projects Help
Guided Project 2-1 SIMnet Project Steps to complete This Project
Mark the steps as checked when you complete them.
1.Open the CentralSierra-02 database file. If a Security Risk message or active content is blocked dialog box displays, click OK or the X to close it.
The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it.
2.Enable content in the security warning.
3.Click the Table Design button [Create tab, Tables group] to open a new table in Design view.
4.Add new fields into the table and set field properties.
5.Type Branch in the Field Name property box of the first row. Press Tab to move to the Data Type property.
6.Select the Short Text Data Type. Press Tab to move to the Description property.
7.Type Unique branch name.
8.Click the Primary Key button [Table Design tab, Tools group].
9.Press Tab to move to the next row.
10.Add the remaining fields into the table using the information in Table 2-18.
Table 2-18
Table 2-18 This table lists the different field names to be typed, the data type to select and the text to be typed in the description property in each of the fields
Field Name
Data Type
Description
Address
Short text
Street address of the branch
Field Name
Data Type
Description
City
Short text
Branch city
Field Name
Data Type
Description
State
Short text
Branch state
Field Name
Data Type
Description
ZIP
Short text
Branch ZIP Code
Field Name
Data Type
Description
Phone
Short text
Branch main phone number
Field Name
Data Type
Description
DateOpened
Date/Time
Date the branch first opened
11.Click the Save button to save the table as Branches. It is a good idea to periodically save a table when you are creating it so you don’t lose your work.
12.Click the row selector to select the Branch field.
13.Type 30 in the Field Size property box in the Field Properties area.
14.Set the Required property to Yes if necessary.
15.Click the row selector to select the Address field.
16.Change the remaining field properties in the Branches table using the information in Table 2-19.
Table 2-19
Table 2-19 This table lists the different field names and the associated text to be typed or selected in the Field Size, Format, Default Value and Required properties
Field Name
Field
Size
17.Format
Default
Value
Required
Address
40
Yes
Field Name
Field
Size
18.Format
Default
Value
Required
City
20
Yes
Field Name
Field
Size
19.Format
Default
Value
Required
State
2
CA
Yes
Field Name
Field
Size
20.Format
Default
Value
Required
ZIP
9
Yes
Field Name
Field
Size
21.Format
Default
Value
Required
Phone
10
Yes
Field Name
Field
Size
22.Format
Default
Value
Required
DateOpened
Medium Date
Yes
23.Save the table
24.Create an input mask for the ZIP field.
25.Click to select the ZIP field.
26.Click the property box for the Input Mask property.
27.Click the Build button to launch the Input Mask Wizard. If a Microsoft Access Security Notice box displays, click Open.
28.Select the Zip Code Input Mask.
29.Click the Next button.
30.Click the Next button without making any changes to the Input Mask or Placeholder character.
31.Select the Without the symbols in the mask, like this radio button if necessary.
32.Click the Finish button.
33.Save the table.
34.Create an input mask for the Phone field.
35.Click to select the Phone field.
36.Click the property box for the Input Mask property.
37.Click the Build button to launch the Input Mask Wizard.
38.Select the Phone Number Input Mask.
39.Click the Next button.
40.Click the Next button without making any changes to the Input Mask or Placeholder character.
41.Select the Without the symbols in the mask, like this radio button if necessary.
42.Click the Finish button.
43.Save the table.
44.Enter records into the Branches table.
45.Change to Datasheet view of the table.
46.Enter the records in Table 2-20 into the Branches table. The DateOpened values will display in the Medium Date format, DD-Mon-YY, after you enter them.
Table 2-20
This table lists the data values to be typed in each of the fields in the Branches table. It contains data for three branches.
Branch
Address
City
State
ZIP
Phone
DateOpened
Cameron Park
3360 Coach Lane
Cameron Park
CA
95682-8454
530-672-3232
1/5/2024
Branch
Address
City
State
ZIP
Phone
DateOpened
Folsom
72 Natoma Street, #B1
Folsom
CA
95630-2640
916-458-5555
6/1/2025
Branch
Address
City
State
ZIP
Phone
DateOpened
Granite Bay
6132 Del Oro Road
Granite Bay
CA
95746-9007
916-791-8787
2/11/2024
47.Save and close the table.
48.Modify the Department field in the Employees table to use a Combo Box.
49.Open the Employees table in Design view.
50.Click the Data Type property box of the Department field, click the arrow, and select Lookup Wizard. The Lookup Wizard dialog box opens. If a Microsoft Access Security Notice box displays, click Open.
51.Select the I will type in the values that I want radio button.
52.Click the Next button.
53.Click the first cell in the Col1 column.
54.Type Administration.
55.Press the down arrow key to move to the next row.
56.Type Health and Benefits. Press the down arrow key to move to the next row.
57.Type Insurance Sales. Press the down arrow key to move to the next row.
58.Type Property and Casualty.
59.Click the Next button.
60.Click the Limit To List check box.
61.Click the Finish button.
62.Save the table. If prompted to test the existing data against the new rules, click Yes.
63.Verify the existence of a foreign key and create a one-to-many relationship between the Branches and Employees tables. The Branch field is the field in common across the two tables. It is the primary key in the Branches table.
64.Open both tables in Design view and verify that the Branch field has the same Data Type and Field Size in both tables.
65.Edit the Description property of the Branch field in the Employees table to read Branch Assigned To – Must match a branch in the Branches table.
66.Save and close any tables that you may have opened.
67.Click the Relationships button [Database Tools tab, Relationships group].
68.Select the Branches table in the Add Tables task pane and click the Add Selected Tables button.
69.Select the Employees table and click the Add Selected Tables button.
70.Click the X in the right corner of the Add Tables task pane to close it.
71.Adjust the size of the table objects to see all of the fields if needed.
72.Drag the Branch field from the Branches table on top of the Branch field in the Employees table. Release the pointer. The Edit Relationships dialog box opens.
73.Verify that the field names displayed are correct. If necessary, click a field name and then click the drop-down arrow to make any corrections.
74.Click the Enforce Referential Integrity check box.
75.Click the Cascade Update Related Fields check box.
Leave the Cascade Delete Related Records check box not selected. The company does not want to delete employees just because they may close a branch. This enables them to reassign employees.
76.Click Create. The Relationships window updates with the relationship line added. The 1 and infinity symbols display indicating that referential integrity is enforced and that there is a one-to-many relationship between the two tables.
77.Save the changes made to the relationships.
78.Click Close [Relationships Design tab, Relationships group].
79.Open the Employees table in Datasheet view and enter the record in Table 2-21 into the table to test the new drop-down list.
Table 2-21
Table 2-21 This table lists the data values to be typed in each of the fields in the Employees table.
EmpID
FirstName
LastName
Branch
Title
Department
StartDate
Base
Monthly
Salary
200
Timothy
Hansen
Cameron Park
Insurance Agent
Health and Benefits
9/15/2026
4200
80.Save and close the table.
81.Open the Branches table in Datasheet view.
82.Adjust the width of the fields so that the entire field contents and field name shows for each field.
83.Click the plus sign to the left of the Branch field for the Cameron Park branch. You should see the 11 employees assigned to Cameron Park.
84.Click the minus sign to close the related records.
Preview the data records in the Branches table before printing.
85.Click the Print button [File tab] to display the printing options.
86.Click the Print Preview button. The Print Preview tab opens and shows a preview of how the table will print.
87.Click the Landscape button [Print Preview tab, Page Layout group] to change the page orientation. The report preview should appear similar to that in Figure 2-82.
A screenshot shows a table in the Print Preview pane.The table is titled Branches with the date 6/15/2025, shown in the top right corner. The table has seven columns and three rows. The column headings are Branch, Address, City, State, ZIP, Phone, and Date Opened. The row entries are as follows: Row 1: Cameron Park, 3360 Coach Lane, Cameron Park, C A, 95682-8454, (530) 672-3232, 05-Jan-24. Row 2: Folsom, 72 Natoma Street, Number B 1, Folsom, C A, 95630-2640, (916) 485-8555, 01-Jun-25. Row 3: Granite Bay, 6132 Del Oro Road, Granite Bay, C A, 95746-9007, (916) 791-8787, 11-Feb-24. Below the table, the text Page 1 is displayed at the center.
Figure 2-82 Print Preview of Branches table records
88.Click the Close Print Preview button.
89.Save and close the Branches table.
90.Close the database.
91.Upload and save your project file.
92.Submit project for grading.