Access 2024 In Practice - Ch 1 Guided Project 1-2

Guided Project 1-2 SIMnet Project Steps to complete This Project

Mark the steps as checked when you complete them.

1.Open the AC2024-GuidedProject-1-2 database start file.

2.Enable content in the security warning. Note: If a Security Risk message displays when opening your database, please do the following: Navigate to where you saved your file. Right-click the file and select Properties. Check the Unblock check box on the General tab. Click Apply and click OK.

The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor.

3.Click the Table button [Create tab, Tables group] to create a new table. A new table opens in Datasheet view.

4.Edit the default primary key and add new fields into the table.

5.Double-click the cell containing the ID field name (column header).

6.Type MemberID to replace ID in the Field name cell and press the Enter key. The Click to Add column to the right is selected so you can add the next field into the table.

7.Select the Short Text data type for this new field.

8.Type FirstName in the Field1 column header and press the Enter key.

9.Select the Short Text data type.

10.Type LastName in the Field1 column header and press the Enter key.

11.Add the remaining fields into the table using the information in Table 1-10. Remember that you select the data type first and then enter the field name.

Table 1-10

Table 1-10 This table lists the different data types to select and the field names to be typed in each of the fields

Data Type Field Name

Short Text Address

Short Text City

Short Text State

Short Text ZIPCode

Hyperlink EmailAddress

Short Text CellPhoneNumber

12.Press Ctrl+S to save all of the modifications you have made to the table.

13.Type Members as the new table name.

14.Click OK.

15.Edit the field properties of the Members table.

16.Click the MemberID field name.

17.Click the Name & Caption button [Table Fields tab, Properties group].

18.Type Member ID in the Caption property. Because the field names in this table provide a clear description of the content, it is not necessary to enter anything into the Description property.

19.Click OK.

20.Select the FirstName field name.

21.Click the Name & Caption button [Table Fields tab, Properties group].

22.Type First Name in the Caption property.

23.Click OK.

24.Type 20 in the Field Size property [Table Fields tab, Properties group] and press Enter.

25.Change the remaining field properties in the table using the information in Table 1-11.

Table 1-11

Table 1-11 This table lists the different field names and the associated text to be typed in the Caption and Size properties

Field Name Caption Size

LastName Last Name 25

Address 45

City 25

State 2

ZIPCode ZIP 5

EmailAddress Email N/A

CellPhoneNumber Cell Phone 12

26.Click the Save button to save the changes to the table. With the changes to the field properties saved, the table is ready for you to begin to enter data records. Remember that even though you have changed the caption that displays as the column header in Datasheet view, the field names have not changed.

27.Enter the member records into the Members table.

28.Click the FirstName cell in the append row.

29.Type Geneva. The pencil icon displays, indicating that this record has not yet been saved.

30.Press the Tab key to move to the next field.

31.Type Lingle in the LastName field. Press the Tab key to move to the next field.

32.Type 1850 Stoneridge Court in the Address field. Press the Tab key.

33.Type Cameron Park in the City field. Press the Tab key.

34.Type CA in the State field. Press the Tab key.

35.Type 95682 in the ZIPCode field. Press the Tab key.

36.Type glingle@gmail.com in the EmailAddress field. Press the Tab key.

37.Type 780-886-6625 in the CellPhoneNumber field. Press the Tab key. The pencil icon no longer displays. You move to the MemberID field in the append row.

Tab to the next field. Enter the information from Table 1-12 into the Members table. Remember that after you tab out of the CellPhoneNumber field you move to the MemberID field. Because MemberID is an AutoNumber field, do not enter a value and simply tab to the next field. Also, because the value for State is CA in each of these records, you can press Ctrl+’ in the State field instead of typing CA to have Access automatically enter the same value from the previous record’s State field.

Table 1-12

Table 1-12 This table lists the data values to be typed in each of the fields in the Members table. It contains data for three members.

FirstName

LastName

Address

City

State

ZIPCode

EmailAddress

Cell PhoneNumber

Megan

Walters

7105 High Street

Folsom

CA

95630

mwalters@mcc.com

916-451-8325

Ryan

Thomas

2227 Alexandra Drive

Auburn

CA

95602

rthomas@gmail.com

916-753-5586

Roy

Baxter

705 First Street

Rocklin

CA

95677

roy@centralsierra.com

780-886-2471

38.Change the layout of the Members table.

39.Click to select the ZIP column.

40.Right-click to open the context menu.

41.Select Field Width.

42.Type 7 in the Column Width cell.

43.Click OK to close the dialog box.

44.Click the Center Alignment button [Home tab, Text Formatting group].

45.Click to select the State column.

46.Place your pointer on the right border of the State field name.

47.Double-click the resize arrow to adjust the Column Width using AutoFit.

48.Click the Center Alignment button.

49.Click the Alternate Row Color drop-down list [Home tab, Text Formatting group] and select Dark Blue 2 (fourth column, third row in the Standard Colors category).

50.Press Ctrl+S to save the changes to the layout.

51.Close the Members table by clicking the X in the tab.

52.Import data from Excel into the Members table.

53.Click the New Data Source button [External Data tab, Import & Link group], select From File and then click Excel to launch the Get External Data – Excel Spreadsheet dialog box.

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

55.Locate and select the MembersDataFile-01 Excel file.

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

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

58.Click the drop-down arrow in the table name box and select the Members table if needed.

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

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

61.Verify that the Import to Table box contains the Members table.

62.Click Finish.

63.Click the Close button without saving the import steps.

64.Open the Members table in Datasheet view. Your table should contain the 14 records shown in Figure 1-111.

A screenshot of Microsoft Access shows a datasheet titled Members.The table consists of nine columns and fifteen rows. The column headings are Member I D, First Name, Last Name, Address, City, State, ZIP, Email, and Cell Phone. The first-row entry is as follows: 1, Geneva, Lingle, 1850 Stoneridge, Cameron Park, C A, 95682, g lingle at gmail dot com, and 780-886-6625. At the bottom of the database, the Status Bar displays the Page number, Filter button, and a Search box.

Figure 1-111 Members table after import

Hide the State field.

65.Select the State column.

66.Click the More button [Home tab, Records group] and then click Hide Fields.

67.Press Ctrl+S to save the changes to the table.

68.Sort the Members table.

69.Click the drop-down arrow to the right of the City field name.

70.Select Sort A to Z on the drop-down list. The records display in ascending order by the City field.

71.Click the Remove Sort button [Home tab, Sort & Filter group] to remove the sort criteria.

72.Click to select the ZIP column.

73.Click, hold, and drag the pointer to move the ZIP column to the left of the Last Name column.

74.Click anywhere to deselect the ZIP column.

75.Click and hold the pointer over the ZIP column. With the selection pointer still displayed, drag the pointer to the Last Name column. Release the pointer.

76.Click the Ascending button [Home tab, Sort & Filter group]. The records display in ascending order by the ZIP column. In cases of the same ZIP Code, the records are sorted in ascending order by the last name. Your table should look similar to Figure 1-112.

A screenshot of Microsoft Access shows the sort indicator arrow in a datasheet.The table consists of five columns and six rows. The column headings are Member I D, First Name, ZIP, Last Name, and Address. The ZIP and Last Name columns are selected. A Sort indicator arrow lies on the right side of the ZIP and Last Name column headings.

77.Sort indicator arrow

Figure 1-112 Records sorted on ZIP and Last Name

78.Click the Remove Sort button [Home tab, Sort & Filter group] to remove the sort criteria.

79.Click anywhere to deselect the columns.

80.Move the ZIP column to the right of the City column.

Filter the Members table using Filter by Selection.

81.Select 916 in the Cell Phone cell for Member ID 2.

82.Click the Selection button [Home tab, Sort & Filter group].

83.Select Begins with “916” from the drop-down list (Figure 1-113). The datasheet should display only the seven members who have a cell phone area code of 916.

A screenshot of Microsoft Access shows the location of the selection button.The screenshot shows the Ribbon at the top. The command groups Sort and Filter, Records, and Find are displayed in the Ribbon. The Sort and Filter command group includes buttons for Ascending, Descending, Remove Sort, and Selection. Within this group, the Selection button is selected and labeled Click the Selection button. The drop-down arrow on the button displays a menu with four options: Begins With "916", Does Not Begin With "916", Contains "916", and Does Not Contain "916". The Begins With "916" option is highlighted and labeled Select the Begins With "916" option. Below the Ribbon, the datasheet consists of five columns and four rows. The column headings are Member I D, First Name, City, ZIP, Email, and Cell Phone. The second row is selected, and its entry is as follows: 2, Megan, Walters, 7105 High Street, Folsom, 95630, m walters at mcc dot com, and 916-451-8325.

84.Select the Begins With "916" option

85.Click the Selection button

Figure 1-113 Filter by Selection

86.Click the drop-down arrow to the right of the Cell Phone field name.

87.Select Clear filter from Cell Phone to remove the filter.

Filter the datasheet using a Text Filter.

88.Click the drop-down arrow to the right of the Email field name.

89.Select Text Filters on the drop-down list and select Contains from the second drop-down list (Figure 1-114). Notice in Figure 1-114 that the second drop-down list displays to the left. The exact position varies based on the width of your screen and location of the Access window.

A screenshot of Microsoft Access shows the Text Filters menu.The datasheet displays five columns and fourteen rows. The column headings are City, ZIP, Email, Cell Phone, and Click to Add, each with a drop-down arrow. The Email column is selected, with the filter drop-down menu open. The menu consists of the following options: Sort A to Z, Sort Z to A, Clear Filter from Email, and Text Filters. The Text Filters option displays checkboxes with all entries in the Email column, along with Select All and Blank options. The expand arrow next to Text Filters opens a submenu with the following options: Equals, Does Not Equal, Begins With, Does Not Begin With, Contains, Does Not Contain, Ends With, and Does Not End With. The OK and Cancel buttons are located at the bottom of the main menu.

Figure 1-114 Text Filter

90.Type gmail in the Email contains box.

91.Click OK. The datasheet should display only the five members who have gmail as part of their email addresses.

92.Click the Toggle Filter button [Home tab, Sort & Filter group] to switch back to viewing all the records.

93.Click the Toggle Filter button again to switch back to the filtered records.

94.Click the drop-down arrow to the right of the Email field name.

95.Select Clear filter from Email to remove the filter.

96.Close the Members table by clicking the X in the tab. Select the No button in the dialog box. Do not save any of the sorting or filtering changes made to the table.

Review the completed Members table.

97.Double-click the Members table to view the table. The table should look similar to Figure 1-115. Note that the sorting and filtering changes were not saved.

A screenshot of Microsoft Access shows a datasheet titled Members.The table consists of eight columns and fifteen rows. The column headings are Member I D, First Name, Last Name, Address, City, ZIP, Email, and Cell Phone. The first-row entry is as follows: 1, Geneva, Lingle, 1850 Stoneridge, Cameron Park, C A, 95682, g lingle at gmail dot com, and 780-886-6625. At the bottom of the database, the Status Bar displays the Page number, Filter button, and a Search box.

Figure 1-115 Completed Members table

98.Close the table by clicking the X in the tab.

99.Add database properties using the Database Properties dialog box.

100.Click the File tab to open the Backstage view.

101.Click the Info button.

102.Click the View and edit database properties link to open the Database Properties dialog box.

103.Type American River Members in the Title area.

104.Type Taylor Mathos in the Author area.

105.Type ARCC in the Company area.

106.Click OK to save the changes.

107.Close the database by clicking the X in the right corner of the application window.

108.Upload and save your project file.

109.Submit project for grading.