Module 1 Getting Started with Access - Skills Review

download

1. Understand relational databases. a. In a Word document, enter your name and the current date. b. Using a bulleted list, identify five advantages of managing database information in Access versus using a spreadsheet.
c. Write a sentence to explain how the terms field, record, table, and relational database relate to one another. d. Save the document with the name IL_AC_1_Database then close it and close Word.

2. Open and explore a database.

a. Start Access.

b. Open the IL_AC_1-2.accdb database from the location where you store your Data Files and save it as IL_AC_1_SupportDesk. Click Enable Content if a yellow Security Warning message appears.

c. Open each of the three tables to study the data they contain. Create and complete the following table in the document you started in the previous step, IL_AC_1_Database.docx.

d. Double-click the CaseListing query in the Navigation Pane to open the query. Change either occurrence of the last name of “Poole” to Fredrick then move to another record to save your changes. Close the CaseListing query.

e. Double-click the EmployeeEntry form in the Navigation Pane to open the form. Use the navigation buttons to navigate through the 20 records to observe each employee’s cases. When you reach the Lisa Fredrick record (record 17 of 20), change her extension value to 8686. Close the EmployeeEntry form.

f. Double-click the CallLog report in the Navigation Pane to open the report. The records are listed in ascending order by employee last name. Scroll through the report to find the “Fredrick, Lisa” record. Close the CallLog report. Note that both the edit to Lisa’s last name and the change to her extension value in previous steps are reflected in the report.

g. In your IL_AC_1_Database.docx document, add one more sentence to explain why the edits to Lisa’s record in previous steps carried through to the CallLog report. Check the spelling, save your changes, then close the IL_AC_1_Database.docx document.

3. Navigate and enter data.

a. Double-click the Employees table to open it, then enter the following record for a new employee: EmployeeID: (AutoNumber) LastName: Curtiss FirstName: Pamela Extension: 8181 Department: Marketing

b. Close the Employees table.

4. Edit existing data.

a. Double-click the Cases table to open it, click the ResolvedDate field for CaseID 1, then enter 4/3/21. Note that you can use the automatic Calendar Picker that assists you when you are entering or updating data in a field with a Date/Time data type. You can also type a date using a month/day/year format.

b. Click the ResolvedDate field for the record with CaseID 5 and enter today’s date. Note that you can enter today’s date from the keyboard or use the CTRL+; shortcut. (Hint: Press and hold the CTRL key while pressing the semicolon ; key.)

c. Edit the CaseTitle value for CaseID 23 to include the word automatically as in “Excel formulas are not automatically updating”.

d. Close the Cases table.

5. Create a table.

a. Click the Create tab on the ribbon, click the Table Design button in the Tables group, then create a new table with the following three fields and data types: 

b. Save the table with the name States. Click No when asked if you want Access to create the primary key field.

6. Modify fields.

a. In Design View of the States table, change the Field Size property for the StateName and Capital fields to 25, and the StateAbbreviation field to 2.

b. Enter an Input Mask property of LL;;* for the StateAbbreviation field. (Hint: Do not use the Input Mask Wizard. Enter the property directly into the Input Mask property box.)

c. Change the Field Name of the StateAbbreviation field to StateAbbrev.

d. Save the States table, then test the Input Mask by entering the first record into the table for Alabama using the following information:

e. Use the Tell Me (Search) box to read about the three parts of an Input Mask property and the meaning of the L character.

f. Close the States table.

7. Create a query.

a. Use the Simple Query Wizard to create a new query with the following fields in the following order: LastName and FirstName from the Employees table, CaseTitle from the Cases table, and CallDateTime from the Calls table.

b. Select a detail query, and title the query CallListing.

c. Display the query datasheet, and change the last name of Mindi Meyers to Perez. Notice that both records that display Mindi’s name change to Perez when you move to a new record. Her name was stored only once in the Employees table but selected twice for this query because she has taken two calls.

d. Save and close the CallListing query.

8. Create a form.

a. Use the Form Wizard to create a new form based on all the fields in the Employees table. Use a Columnar layout and title the form EmployeeMaster.

b. Use the record navigation buttons to navigate to the third record to confirm that Mindi Meyers has been changed to Mindi Perez.

c. Save and close the EmployeeMaster form.

9. Create a report.

a. Use the Report Wizard to create a new report based on all the fields of the Employees table.

b. Group the records by Department, and sort them in ascending order by LastName and then FirstName.

c. Use a stepped layout and a landscape orientation.

d. Title the report EmployeeMasterList, and preview it as shown in FIGURE 1-21.

e. Use the navigation buttons to locate the Mindi Perez record (in the Research department) to confirm that the report is also based on the updated data.

f. Save and close the EmployeeMasterList report.

10. Save and share a database with OneDrive.

a. Close the IL_AC_1_SupportDesk.accdb database.

b. Log into your Microsoft OneDrive.com account.

c. Create a Module1 folder (if you have not already done so).

d. Upload the IL_AC_1_SupportDesk.accdb database to the Module1 folder.

e. Through email, share the IL_AC_1_SupportDesk.accdb database with your instructor.

11. Create a new database.

a. Start Access and use the Blank desktop database to create a new database named IL_AC_1_CustomerSurvey in the folder where you store your Data Files.

b. Close the Table1 table without saving it.

c. Build the first table by importing a listing of states and provinces from an existing Excel spreadsheet named Support_IL_AC_1_StatesAndProvs.

d. The first row contains column headings. Accept the default field options, but choose StateAbbrev as the primary key field.

e. Name the new table StatesAndProvs, and do not save the import steps.

f. Open the StatesAndProvs table in Datasheet View to confirm that 64 records were imported, then close the StatesAndProvs table.

12. Compact and back up a database.

a. On the Database Tools tab, compact and repair the IL_AC_1_CustomerSurvey.accdb database.

b. Create a backup of the IL_AC_1_CustomerSurvey.accdb database in the folder where you store your Data Files. Be sure to use the Back Up Database option so that the current date is automatically appended to the filename.

c. View your Account settings, and in a Word document, note your existing Connected Services.

d. Using the Tell me what you want to do feature, research Connected Services and pick one of the services to explore further. Identify which Connected Service you chose in your Word document, write at least one sentence explaining why you chose it and one sentence describing the features it offers. Save the document with the name IL_AC_1_AccessConnectedServices.

e. Close the IL_AC_1_CustomerSurvey.accdb database and Access.