In this project, you will continue to work with the Computer Science department database from the Chapter 1 Skill Review
1.1. It uses Access to manage employees and various items that are loaned to students and faculty. You will create two new tables in this database: one for the companies that the department frequently purchases from and another with a list of classrooms.
Skills needed to complete this project:
• Designing a Table
• Creating and Saving a Table in Datasheet View
• Renaming Fields
• Changing Data Type
• Adding Fields in Datasheet View
• Using Quick Start to Add Related Fields
• Adjusting Table Column Widths
• Applying an Input Mask from Design View
• Adding a Lookup Field from Another Table
• Formatting Fields
• Modifying Field Properties
• Adding a Total Row to a Table
• Working with Attachment Fields
• Deleting Fields
• Creating a Table in Design View
• Setting the Primary Key
• Inserting Fields in Design View
• Adding a Lookup Field from a List
• Creating Relationships
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 start file AC2013-SkillReview-2-1.
2. If necessary, enable active content by clicking the Enable Content button in the Message Bar.
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.
a. On the Create tab, in the Tables group, click the Table button.
b. 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.
c. Create the next field by typing Greg's College Supplies in the cell directly underneath the Click to Add heading.
d. Press Tab to go to the next field in this record.
e. Create another new field by typing: www.gregscollegesupplies.com
f. Press Tab again.
g. Go to the next row in the table and enter another record with the following: Cindy's Business Supplies www.cindysbusinesssupplies.com
5. Rename the fields.
a. Right-click the ID field heading, and click Rename Field.
b. Type VendorID and press Enter.
c. Repeat the process for Field1, renaming it: CompanyName
d. Repeat the process for Field2, renaming it: WebSite
6. The data type for the WebSite field is Short Text. Change it to Hyperlink.
a. Click the WebSite column header to select the field.
b. On the Table Tools Fields tab, in the Formatting group, expand the Data Type list, and select Hyperlink.
7. Add a new field to the table to store phone numbers.
a. Click the arrow next to the Click to Add heading in the last available field, and select Short Text.
b. Type Phone to overwrite the default field name Field1.
8. Add a group of related fields using Quick Start.
a. Click the cell underneath the last Click to Add heading.
b. On the Table Tools Fields tab, in the Add & Delete group, click the More Fields button.
c. Scroll down and select Address from the Quick Start category.
d. Observe the five new fields. Type the following data into these new fields:
|
Address |
City |
StateProvince |
ZipPostal |
Country Region |
| e |
370 Pine St |
Phoenix |
Arizona |
85018 |
USA |
| f |
900 Finch Way |
Phoenix |
Arizona |
85013 |
USA |
9. Resize all of the columns in this table to the best fit possible by double-clicking the right edge of their field headings.
10. Save the table.
a. On the Quick Access Toolbar, click the Save button.
b. In the Save As dialog, type Vendors in the Table Name box.
c. Click OK.
11. Switch to Design view.
a. On the Home tab, in the Views group, click the View button to switch to Design view.
b. Observe that when you created the new table, Access automatically assigned the VendorID field as the primary key.
12. Add an input mask to the new Phone field to force users to enter data in the (206) 555-1212 format.
a. Select the Phone field by clicking anywhere in that row.
b. In the Field Properties pane, click the Input Mask box, and then click the Build... button to start the Input Mask Wizard.
c. 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.
d. Click the Next button to continue without making any changes to the input mask or the placeholder character.
e. Verify that the radio button to store the data without the symbols is selected, and click Next.
f. Click Finish.
g. Observe that the Input Mask box now displays the input mask format: !\(999") "000\-0000;;_
h. Switch back to Datasheet view by clicking the Datasheet View button at the lower right part of the status bar.
i. When Access prompts you to save the table, click Yes.
j. Under this field heading, enter the following phone numbers: (623)555-6810 for Greg's and (623)555-8200 for Cindy's. Notice how the input mask adds the correct characters to the phone number and will prevent you from typing any character other than a number.
13. Close the Vendors table. If Access prompts you to save the changes to the table, click Yes.
14. Create a CompanyName lookup field in the Items table using values from the Vendors table.
a. Open the Items table in Datasheet view.
b. Click the arrow next to the Click to Add heading in the last available field, and select Lookup & Relationship.
c. 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.
d. Select Table: Vendors as the table that will provide the values for your lookup field, and click Next.
e. From the Available Fields list, select the CompanyName field and click the single > button to add it to the right. Click Next.
f. Click the arrow to expand the 1 list and choose CompanyName as the sort field. Observe that even though you added only the CompanyName field to the lookup list, Access included the VendorID field (the primary key) automatically. Click Next.
g. Verify that the Hide key column (recommended) check box is checked and that the two companies you entered in the table earlier appear in the lookup field preview. Click Next.
h. In the last screen, type CompanyName as the label for this new field.
i. Limit data entry to the values in the list by clicking the Enable Data Integrity check box.
j. Click Finish.
k. Use this new lookup field to add CompanyName values for the first three records. Choose Greg's for the first two and Cindy's for the third.
15. Notice that many of the columns in the table are too narrow and the data are not fully visible.
a. Resize the ItemName and CompanyName columns to the best fit possible by double-clicking the right edge of their field headings.
b. Resize the Description column to be exactly 45 wide.
i. Click the Description column header to select the field.
ii. On the Home tab, in the Records group, click the More button, and select Field Width.
iii. In the Column Width dialog, type 45 in the Column Width box.
iv. Click OK.
16. Modify the Cost field to use the Currency format.
a. Click the Cost column header to select the field.
b. On the Table Tools Fields tab, in the Formatting group, click the Apply Currency Format button.
c. Notice that the Format box now displays Currency.
17. Modify the size of the ItemID field.
a. Click the ItemID field heading.
b. On the Table Tools Fields tab, in the Properties group, type 4 in the Field Size box. Press Enter.
c. Click Yes to continue.
d. If Access shows additional messages, click OK to dismiss each one. Changing the field size to 4 will not delete any data or delete the field.
18. Add a Total row to the datasheet to display the sum of the values in the Cost field.
a. On the Home tab, in the Records group, click the Totals button.
b. In the new Total row, click the cell in the Cost column, expand the list, and select Sum.
c. Observe that the Totals button appears highlighted. Click it again, and notice that the button is no longer highlighted and the Total row is hidden.
d. Click the Totals button again. The Total row appears again, still displaying the sum of the values in the Cost field.
19. Add an Attachment field and an attachment.
a. Click the arrow next to the Click to Add heading in the last available field. Select the Attachment option.
b. Find the record with an ID of LAS1.
c. Double-click the paperclip icon for this record, which is located in the new Attachment column you just created.
d. Click Add in the Attachments dialog and then find the file named laser_pointer.jpg in your student data files folder.
e. Double-click the file and then click OK. Note the (1) added to the paperclip icon to indicate that the record has one attachment.
20. Delete the Location field from the Items table.
a. Click the Location field column heading to select the field.
b. On the Table Tools Fields tab, in the Add & Delete group, click the Delete button.
c. Click Yes to confirm the deletion.
21. Save and close the Items table.
22. Close any open tables. If Access prompts you to save changes, click Yes.
23. Create a table in Design view:
a. On the Create tab, in the Tables group, click the Table Design button.
b. Type RoomNo for the first field name. Press Tab.
c. Accept the default data type, Short Text.
d. With the cursor still in this row, on the Design tab, in the Tools group, click the Primary Key button.
e. Create the following fields in Design view:
|
FieldName |
DataType |
Description |
| f. |
Capacity |
Number |
Maximum number of students |
| g. |
UpgradeDate |
Date/Time |
Date when the instructor's computer was lasr upgraded |
24. Save the table.
a. On the Quick Access Toolbar, click the Save button.
b. In the Save As dialog, type Classrooms in the Table Name box.
c. Click OK.
25. Modify field properties and formatting in Design view.
a. Click anywhere in the UpgradeDate row. In the Field Properties pane, click in the Format box. Click the arrow to expand the selection list, and select Medium Date.
b. Click anywhere in the Capacity field. In the Field Properties pane, click in the Default Value box. Type: 40
26. Add a new lookup field to the Classrooms table to use values you enter yourself.
a. Click in the first empty cell in the Field Name column and type: Type
b. Press Tab or click in the Data Type cell. Expand the selection list, and select Lookup Wizard...
c. In the Lookup Wizard, click the I will type in the values I want. radio button. Click Next.
d. Use only 1 column and enter the following three values: Auditorium Computer Lab Lecture Room
e. Click Next, and verify that Type is the label for the lookup field.
f. Limit data entry to the values in the list by clicking the Limit to List check box.
g. Click Finish.
27. Save and close the table.
28. Review the relationship between the Vendors table and the Items table.
a. Open the Relationships window. On the Database Tools tab, in the Relationships group, click the Relationships button.
b. Show all tables. On the Relationship Tools Design tab, in the Relationships group, click the All Relationships button.
c. There is a relationship between the VendorID field in the Vendors table and the CompanyName field in the Items table. This relationship was created when you created the CompanyName lookup field in the Items table.
d. Double-click the line connecting the two field names to open the Edit Relationships dialog.
e. Look at the Relationship Type box near the bottom of the dialog and note that the relationship type is one-to-many.
f. Verify that entries in the CompanyName field will have matching entries in the VendorID field by noting that the Enforce Referential Integrity check box is checked.
g. Click OK.
29. Create a new relationship between the EmployeeID field in the Employees table and the EmployeeID field in the Loans table.
a. Click the EmployeeID field in the Employees table and drag it to the EmployeeID field in the Loans table.
b. The Edit Relationships dialog opens.
c. Click the Enforce Referential Integrity check box.
d. Click Create.
e. Observe the new line connecting the Employees table and the Loans table.
30. Close the Relationships window. If Access prompts you to save changes to the layout, click Yes.
31. Close the database and exit Access.
32. Upload and save your file.
33. Submit project for grading.
Attachment:- Assignment.rar