ACCESS 2021 2.1

Skill Review 2.1

In this project, you will continue to work with the Computer Science department database from the Chapter 1 Skill Review 1.1. You will create new tables and modify fields in existing tables. You will also create lookup lists—one where you enter the values yourself and another where the values come from a field in another table. You will modify the relationship created by the lookup field to ensure that deletions in one table carry through to the related table.

Skills needed to complete this project:

• Designing a Table (Skill 2.1)
• Creating and Saving a Table in Datasheet View (Skill 2.2)
• Renaming Fields and Changing the Field Caption (Skill 2.3)
• Adding Fields in Datasheet View (Skill 2.4)
• Using Quick Start to Add Related Fields (Skill 2.5)
• Changing Data Type (Skill 2.10)
• Applying an Input Mask (Skill 2.14)
• Adding a Lookup Field from a List (Skill 2.17)
• Inserting, Deleting, and Moving Fields in Design View (Skill 2.9)
• Formatting Fields in a Table (Skill 2.11)
• Adding a Lookup Field from Another Table (Skill 2.16)
• Modifying the Field Size Property (Skill 2.13)
• Working with Attachment Fields (Skill 2.15)
• Deleting Fields in Datasheet View (Skill 2.6)
• Creating a Table in Design View and Setting the Primary Key (Skill 2.8)
• Creating Relationships (Skill 2.18)
• Enforcing Deletions and Updates in Relationships (Skill 2.19)

Important: Download the resource files needed for this project from the Resources link. If they download in a zipped folder, make sure to extract the files after downloading the resources zipped folder. Visit the SIMnet instant help for step-by-step instruction.

Steps to complete this project:

Mark the steps as checked when you complete them.

1. Open the start file AC2021-SkillReview-2-1.
2. If the database opens in Protected View, click the Enable Content button in the Message Bar at the top of the database so you can modify it.
3. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor.
4. Create a table in Datasheet view to store vendor company data.On the Create tab, in the Tables group, click the Table button.You are now in the Datasheet view of a new table. Notice that Access has created a new field named ID with the AutoNumber data type.
5. Save the table.Press Ctrl + S.In the Save As dialog, type Vendors in the Table Name box.Click OK.
6. Continue adding fields to the new table.Create the next field by typing Amazon in the cell directly underneath the Click to Add heading.Press Tab to go to the next field in this record.Create another new field by typing: www.amazon.comPress Tab again.Go to the next row in the table and enter another record with the following:Stapleswww.staples.com
7. Rename the fields.Click anywhere in the ID field.On the Table Fields tab, in the Properties group, click the Name & Caption button.Type VendorID in the Name box and press Tab.Type Vendor ID in the Caption box and click OK.Repeat the process for Field1, renaming it CompanyName with the caption Company Name.Repeat the process for Field2, renaming it WebSite with the caption Web Site.
8. Add a group of related fields using Quick Start.Click the cell underneath the last Click to Add heading.On the Table Fields tab, in the Add & Delete group, click the More Fields button.Scroll down and select Address from the Quick Start category.
9. Close the table.
10. In the Employees table, modify the data type for the Email field to Hyperlink.In the Navigation Pane, double-click Employees : Table to open it in Datasheet view.Click anywhere in the Email column to select the field. If necessary, click the Table Fields tab. Observe that the data type for this field is Short Text.On the Table Fields tab, in the Formatting group, expand the Data Type list, and select Hyperlink.
11. Add an input mask to the new Phone field to force users to enter data in the (206) 555-1212 format.On the Table Fields tab, in the Views group, click the View button to switch to Design view.Select the Phone field by clicking anywhere in that row.In the Field Properties pane, click the Input Mask box, and then click the Build… button to start the Input Mask Wizard.The first input mask sample is the phone number format you want. Test it by typing any sample phone number in the Try It box. Click Next to continue.Click the Next button to continue without making any changes to the input mask or the placeholder character.Verify that the radio button to store the data without the symbols is selected, and click Next.Click Finish.Observe that the Input Mask box now displays the input mask format:!\(999″) “000\-0000;;_Save the table.Switch back to Datasheet view by clicking the Datasheet View button at the lower right part of the status bar. Observe the change to the Phone field. Enter a new record with any data you want. Observe the input mask as you enter data in the Phone field.
12. Close the Employees table.
13. In the Items table, move the Category field so it is between the ItemName and Description fields.In the Navigation Pane, right-click Items : Table and select Design View.Click the row selector at the left of the Category field.Click and drag until the black line is between the ItemName and Description fields.Release the mouse button.
14. Modify the Category field to use a lookup list.Click in the Data Type column next to Category and select Lookup Wizard…In the Lookup Wizard, click the I will type in the values I want. radio button. Click Next.Use only 1 column and enter the following values:Type Equipment and then press Tab.Type Software and then press Tab.Type Textbook and then press Tab.Type Other and then click Next.Verify that Category is the label for the lookup field.Limit data entry to the values in the list by clicking the Limit to List check box.Click Finish.Save the table.
15. Modify the Cost field to use the Currency format.On the Home tab, in the Views group, click the View button to switch to Datasheet view.Select the Cost field by clicking anywhere in that column.On the Table Fields tab, in the Formatting group, click the Apply Currency Format button.Notice that the Format box now displays Currency.
16. Modify the size of the ItemID field.Click anywhere in the ItemID field.On the Table Fields tab, in the Properties group, type 4 in the Field Size box. Press Enter.Click Yes to continue.
17. Add an Attachment field.Click the arrow next to the Click to Add heading in the last available field to expand the list of field types.Select the Attachment option.
18. Add an attachment to a record.Find the record with an ID of PRJ1.Double-click the paperclip icon for this record, which is located in the new Attachment column you just created.Click Add in the Attachments dialog and then find the file named projector.jpg in your student data files folder.Double-click the file and then click OK. Note the (1) added to the paperclip icon to indicate that the record has one attachment.
19. Delete the Location field from the Items table.Click the Location field column heading to select the field.On the Table Fields tab, in the Add & Delete group, click the Delete button.Click Yes to confirm the deletion.
20. Save and close the Items table.
21. Modify the ItemID field in the Loans table to use a lookup list of values from the Items table.In the Navigation Pane, right-click Loans : Table and select Design View.Click in the Data Type column next to ItemID and select Lookup Wizard…In the Lookup Wizard, verify that the I want the lookup field to get the values from another table or query. radio button is selected, and click Next.Select Table: Items as the table that will provide the values for your lookup field, and click Next.From the Available Fields list, select the ItemID field and click the single > button to add it to the Selected Fields list. Select the ItemName field and click the single > button to add it to the Selected Fields list. Click Next.Click the arrow to expand the 1 list and choose ItemName as the sort field. Click Next.Uncheck the Hide key column (recommended) check box. Verify that both ItemID and ItemName field data appear in the list.Double-click the right edge of the ItemName column to autofit the column width to the data. Click Next.Verify that the ItemID field is selected to store in the database. Click Next.Limit data entry to the values in the list by clicking the Enable Data Integrity check box.Click Finish.Click Yes to save the table. Click Yes again to continue.
22. Close the Loans table.
23. Create a table in Design view:On the Create tab, in the Tables group, click the Table Design button.Type RoomNo for the first field name. Press Tab.Accept the default data type, Short Text.With the cursor still in this row, on the Table Design tab, in the Tools group, click the Primary Key button.Create the following fields in Design view:Table AC 2.1 displays the field name, datatype, and description information for the Classrooms Table.FieldNameDataTypeDescriptionCapacityNumberMaximum number of seatsComputerLabYes/NoIs this room a computer lab?
24. Save the table.Press Ctrl + S.In the Save As dialog, type Classrooms in the Table Name box.Click OK.
25. Switch to Datasheet view to review the fields you entered and then close the table.
26. Create a new relationship between the EmployeeID field in the Employees table and the EmployeeID field in the Loans table.Open the Relationships window. On the Database Tools tab, in the Relationships group, click the Relationships button.Click the EmployeeID field in the Employees table and drag it to the EmployeeID field in the Loans table.The Edit Relationships dialog opens.Click the Enforce Referential Integrity check box.Click Create.Observe the new line connecting the Employees table and the Loans table.
27. Modify the relationship between the Loans table and the Items table so when an item is deleted any loans for that item are deleted as well.There is a relationship between the ItemID field in the Items table and the ItemID field in the Loans table. This relationship was created when you created the ItemID lookup field in the Loans table. Double-click the line connecting the two field names to open the Edit Relationships dialog.Look at the Relationship Type box near the bottom of the dialog and note that the relationship type is one-to-many.Click the Cascade Delete Related Records check box.Click OK.
28. Close the Relationships window. If Access prompts you to save changes to the layout, click Yes.
29. Close the database and exit Access.
30. Upload and save your project file.
31. Submit project for grading.