Access 2021 Chapter 1 Guided Project 1-2 - Creating a Database and Tables - Update 2025 Full answer

Guided Project 1-2
American River Cycling Club is a local cycling club. For this project, you create a database, containing
one table, to store information about club members. After creating the database, add fields into the table,
edit field properties, enter records, modify the datasheet layout, import data and sort and filter the data in
the table.
[Student Learning Outcomes 1.2, 1.3, 1.4, 1.5, 1.6]
DO NOT use the files below if you are completing an autograded SIMnet Project. Using the linked files
below will prevent you from uploading and submitting your work in SIMnet. The files for all autograded
SIMnet Projects MUST be downloaded from your class assignment.
File Needed: MembersDataFile-01.xlsx
Completed Project File Name: [your initials] Access 1-2.accdb
Skills Covered in This Project
Create a blank desktop database.
Edit the default primary key.
Add a new field into a table.
Edit properties in Datasheet view.
Save a table.
Enter records.
Change the datasheet layout.
Import records.
Sort data in a table.
Filter data in a table.
This image appears when a project instruction has changed to accommodate an update to
Microsoft Office 365. If the instruction does not match your version of Office, try using the alternate
instruction instead.
1. Create a new, blank desktop database.
a. Open Access or click the New button [File tab] if an Access database is already open.
b. Click Blank desktop database.
c. Enter [your initials] Access 1-2 in the File Name box.
d. Click the Browse button to change the location in which to save your database and click OK.
e. Click the Create button. The database is created. A new table is created and opens in Datasheet
view. If a table does not display, click the Table button [Create tab, Tables group] to create a new
table.
2. Edit the default primary key and add new fields into the table.
a. Double-click the cell containing the ID field name (column header).
b. Type MemberID in the Field name 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.
c. Select the Short Text data type for this new field.
d. Type FirstName in the Field1 column header and press the Enter key.
e. Select the Short Text data type.
f. Type LastName in the Field1 column header and press the Enter key.
g. Add the remaining fields into the table using the information in the nearby table. Remember that
you select the data type first and then enter the field name. After you enter the last field name
CellPhoneNumber, press the down arrow key to remain in that column.
Data Type
Field Name
Short Text
Short Text
Address
City
Short Text
Short Text
State
ZIPCode
Hyperlink
Short Text
EmailAddress
CellPhoneNumber
h. Press Ctrl+S to save all of the modifications you have made to the table.
i. Type Members as the new table name.
j. Click OK.
3. Edit the field properties of the Members table.
a. Click the MemberID field name.
b. Click the Name & Caption button [Table Tools Fields tab, Properties group].
c. 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.
d. Click OK.
e. Select the FirstName field name.
f. Click the Name & Caption button [Table Tools Fields tab, Properties group].
g. Type First Name in the Caption property.
h. Click OK.
i. Type 20 in the Field Size property [Table Tools Fields tab, Properties group].
j. Change the remaining field properties in the table using the following information:
Field Name
Caption
LastName
Last Name
Size
25
Address
City
45
25
State
2
ZIPCode
EmailAddress
ZIP
Email
5
N/A
CellPhoneNumber
Cell Phone
12
k. 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.
4. Enter the member records into the Members table.
a. Click the FirstName cell in the append row.
b. Type Geneva. The Pencil icon displays, indicating that this record has not yet been saved.
c. Press the Tab key to move to the next field.
d. Type Lingle in the LastName field. Press the Tab key to move to the next field.
e. Type 1850 Stoneridge Court in the Address field. Press the Tab key.
f. Type Cameron Park in the City field. Press the Tab key.
g. Type CA in the State field. Press the Tab key.
h. Type 95682 in the ZIPCode field. Press the Tab key
. Type glingle@gmail.com in the EmailAddress field. Press the Tab key.
j. 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.
k. Tab to the next field. Enter the following information into the Members table. Remember that after
you tab out of the CellPhoneNumber field you move to the MemberID field. Since that is an
AutoNumber field, do not enter a value and simply tab to the next field.
FirstName LastName Address City
State ZIPCode EmailAddress
Cheryl
Ryan
Wilson
Thomas
7105 High
Street
2227
Alexandra
Drive
Folsom CA 95630
Auburn CA 95602
cwilson@mcc.com
CellPhoneNumber
916-451-8325
rthomas@gmail.com
916-753-5586
Roy
Baxter
705 First
Street
Rocklin CA
5. Change the layout of the Members table.
a. Click to select the ZIP column.
b. Right-click to open the context menu.
c. Select Field Width.
d. Type 7 in the Column Width cell.
e. Click OK to close the dialog box.
95677
roy@centralsierra.com 780-886-2471
f. Click the Center Alignment button [Home tab, Text Formatting group].
g. Click to select the State column.
h. Place your pointer on the right border of the State field name.
i. Double-click the resize arrow to adjust the Field Width using AutoFit.
j. Click the Center Alignment button.
k. Click the Alternate Row Color drop-down list [Home tab, Text Formatting group] and select
Blue-Gray, Text 2, Lighter 80% (fourth column, second row in the Theme Colors category).
l. Press Ctrl+S to save the changes to the layout.
m. Close the Members table by clicking the X in the right corner.
6. Import data from Excel into the Members table.
a. Click the New Data Source button [External Data tab, Import & Link group] select From File
and then select Excel to launch the Get External Data — Excel Spreadsheet dialog box.
Click the Excel button [External Data tab, Import & Link group] to launch the Get
External Data – Excel Spreadsheet dialog box.
b. Click the Browse button to launch the File Open dialog box.
c. Locate and select the MembersDataFile-01 Excel file.
d. Click the Open button. The File Open dialog box closes.
e. Select the Append a copy of the records to the table radio button.
f. Click the drop-down arrow in the table name box and select the Members table if needed.
g. Click OK to launch the Import Spreadsheet Wizard. The data records should display in the Import
Spreadsheet Wizard window.
h. Click the Next button to advance to the next page of the Import Spreadsheet Wizard.
i. Verify that the Import to Table box contains the Members table.
j. Click Finish.
k. Click the Close button.
7. Open the Members table in Datasheet view. Your table should contain the 14 records shown in Figure
1-108.
8. Sort the Members table.
a. Click the drop-down arrow to the right of the City field name.
b. Select Sort A to Z on the drop-down list. The records display in ascending order by the City field.
c. Click the Remove Sort button [Home tab, Sort & Filter group] to remove the sort criteria.
d. Click to select the ZIP column.
e. Click, hold, and drag the pointer to move the ZIP column to the left of the Last Name column.
f. Click anywhere to deselect the ZIP column.
g. 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.
h. 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-109.
i. Click the Remove Sort button [Home tab,
Sort & Filter group] to remove the sort
criteria.
j. Move the ZIP column to the right of the State
column.
9. Filter the Members table using Filter by
Selection.
a. Select 916 in the Cell Phone cell for Member ID 2.
1-109 Records sorted on ZIP and Last Name
b. Click the Selection button [Home tab, Sort & Filter group].
c. Select Begins with “916” from the drop-down list (Figure 1-110).
The datasheet should display only the seven members who have a cell phone area code of 916.
d. Click the drop-down arrow to the right of the Cell Phone field name.
e. Select Clear filter from Cell Phone to remove the filter.
10. Filter the datasheet using a Text Filter.
a. Click the drop-down arrow to the right of
the Email field name.
b. Select Text Filters on the drop-down list
and select Contains from the second drop
down list (Figure 1-111). Notice in Figure 1
111 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.
c. Type gmail in the Email contains box.
d. Click OK. The datasheet should display only
the five members who have gmail as part of
their email addresses.
e. Click the Toggle Filter button [Home tab,
Sort & Filter group] to switch back to viewing
all the records.
f. Click the Toggle Filter button again to switch
back to the filtered records
g. Click the drop-down arrow to the right of the Email field name.
h. Select Clear filter from Email to remove the filter.
11. Close the Members table by clicking the X in the right corner. Select the No button in the dialog box.
Do not save any of the changes made to the table.
12. Review the completed Members table.
a. Double-click the Members table to view the table. The table should be similar to Figure 1-112. Note
that the sorting and filtering changes were not saved.
b. Close the table by clicking the X in the right corner.
1-112 Completed Members table
13. Add database properties using the Database Properties dialog box.
a. Click the File tab to open the Backstage view. The Info button should be selected.
b. Click the View and Edit Database Properties link to open the Database Properties dialog box.
c. Type American River Members in the Title area.
d. Type Taylor Mathos in the Author area.
e. Type ARCC in the Company area.
f. Click OK to save the changes.
14. Close the database by clicking the X in the right corner of the application window

#MicrosoftOffice
#MicrosoftAccess
#Microsoft
#SIMNet
#Database
#Access2021