I have a Spread sheet and Data class for Human Resource. I have instructions for the sheets and they may require Macros and Excel. Are you able to do the assignment? Who can do this and complete it by
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, hide table fields, import data and sort and filter the data in the table.
File Needed: AC2019-GuidedProject-1-2.accdb (Available from the Start File link.), MembersDataFile-01.xlsx (Available from the Resources link.)
Completed Project File Name: [First Name.Last Name]-AC2019-GuidedProject-1-2.accdb
Skills Covered in This Project
Create a blank 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.
Change the datasheet layout.
Hide table fields.
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 365 Apps. If the instruction does not match your version of Office, try using the alternate instruction instead.
Steps to complete This Project
Mark the steps as checked when you complete them.
Open the AC2019-GuidedProject-1-2 database start file.
Enable content in the security warning.
The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor.
Click the Table button [Create tab, Tables group] to create a new table. A new table opens in Datasheet view.
Edit the default primary key and add new fields into the table.
Double-click the cell containing the ID field name (column header).
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.
Select the Short Text data type for this new field.
Type FirstName in the Field1 column header and press the Enter key.
Select the Short Text data type.
Type LastName in the Field1 column header and press the Enter key.
Add the remaining fields into the table using the information in Table 1-8. Remember that you select the data type first and then enter the field name.
Table 1-8
Press Ctrl+S to save all of the modifications you have made to the table.
Type Members as the new table name.
Click OK.
Edit the field properties of the Members
Click the MemberID field name.
Click the Name & Caption button [Fields tab, Properties group]. Click the Name & Caption button [Table Tools Fields tab, Properties group].
Type Member ID in the Caption 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.
Click OK.
Select the FirstName field name.
Click the Name & Caption button [Fields tab, Properties group]. Click the Name & Caption button [Table Tools Fields tab, Properties group].
Type First Name in the Caption
Click OK.
Type 20 in the Field Size property [Fields tab, Properties group]. Type 20 in the Field Size property [Table Tools Fields tab, Properties group].
Change the remaining field properties in the table using the information in Table 1-9.
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.
Enter the member records into the Members table.
Click the FirstName cell in the append row.
Type Geneva. The pencil icon displays, indicating that this record has not yet been saved.
Press the Tab key to move to the next field.
Type Lingle in the LastName field. Press the Tab key to move to the next field.
Type 1850 Stoneridge Court in the Address field. Press the Tab key.
Type Cameron Park in the City field. Press the Tab key.
Type CA in the State field. Press the Tab key.
Type 95682 in the ZIPCode field. Press the Tab key.
Type [email protected] in the EmailAddress field. Press the Tab key.
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-10 into the Members table. Remember that after you tab out of the CellPhoneNumber field you move to the MemberID field. Since MemberID is an AutoNumber field, do not enter a value and simply tab to the next field.
Change the layout of the Members table.
Click to select the ZIP column.
Right-click to open the context menu.
Select Field Width.
Type 7 in the Column Width cell.
Click OK to close the dialog box.
Click the Center Alignment button [Home tab, Text Formatting group].
Click to select the State column.
Place your pointer on the right border of the State field name.
Double-click the resize arrow to adjust the Column Width using AutoFit.
Click the Center Alignment button.
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).
Press Ctrl+S to save the changes to the layout.
Close the Members table by clicking the X in the right corner.
Import data from Excel into the Members table.
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 Browse button to launch the File Open dialog box.
Locate and select the MembersDataFile-01 Excel file.
Click the Open button. The File Open dialog box closes.
Select the Append a copy of the records to the table radio button.
Click the drop-down arrow in the table name box and select the Members table if needed.
Click OK to launch the Import Spreadsheet Wizard. The data records should display in the Import Spreadsheet Wizard window.
Click the Next button to advance to the next page of the Import Spreadsheet Wizard.
Verify that the Import to Table box contains the Members table.
Click Finish.
Click the Close button.
Open the Members table in Datasheet Your table should contain the 14 records shown in Figure 1-111.
Hide the State field.
Select the State column.
Click the More button [Home tab, Records group] and then select Hide Fields.
Press Ctrl+S to save the changes to the table.
Sort the Members table.
Click the drop-down arrow to the right of the City field name.
Select Sort A to Z on the drop-down list. The records display in ascending order by the City field.
Click the Remove Sort button [Home tab, Sort & Filter group] to remove the sort criteria.
Click to select the ZIP column.
Click, hold, and drag the pointer to move the ZIP column to the left of the Last Name column.
Click anywhere to deselect the ZIP column.
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.
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.
Figure 1-112 Records sorted on ZIP and Last Name
Click the Remove Sort button [Home tab, Sort & Filter group] to remove the sort criteria.
Click anywhere to deselect the columns.
Move the ZIP column to the right of the City column.
Filter the Members table using Filter by Selection.
Select 916 in the Cell Phone cell for Member ID 2.
Click the Selection button [Home tab, Sort & Filter group].
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.
Figure 1-113 Filter by Selection
Click the drop-down arrow to the right of the Cell Phone field name.
Select Clear filter from Cell Phone to remove the filter.
Filter the datasheet using a Text Filter.
Click the drop-down arrow to the right of the Email field name.
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.
Figure 1-114 Text Filter
Type gmail in the Email contains box.
Click OK. The datasheet should display only the five members who have gmail as part of their email addresses.
Click the Toggle Filter button [Home tab, Sort & Filter group] to switch back to viewing all the records.
Click the Toggle Filter button again to switch back to the filtered records.
Click the drop-down arrow to the right of the Email field name.
Select Clear filter from Email to remove the filter.
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.
Review the completed Members table.
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.
Figure 1-115 Completed Members table
Close the table by clicking the X in the right corner.
Add database properties using the Database Properties dialog box.
Click the File tab to open the Backstage view. If necessary, click Info.
Click the View and edit database properties link to open the Database Properties dialog box.
Type American River Members in the Title area.
Type Taylor Mathos in the Author area.
Type ARCC in the Company area.
Click OK to save the changes.
Close the database by clicking the X in the right corner of the application window.
Upload and save your project file.
Submit project for grading.
