New Perspectives Access 2019 | Modules 1-4: SAM Capstone Project 1b Southeast County Library System

GETTING STARTED

1. Open the file NP_AC19_CS1-4b_FirstLastName_1.accdb, available for download from the SAM website.

2. Save the file as NP_AC19_CS1-4b_FirstLastName_2.accdb by changing the “1” to a “2”.

a. If you do not see the .accdb file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.

3. To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:

Support_AC19_CS1-4b_Locations.xlsx

4. Open the _GradingInfoTable table and ensure that your first and last name is displayed as the first record in the table. If the table does not contain your name, delete the file and download a new copy from the SAM website.

PROJECT STEPS

1. The Southeast County Library System currently includes a main library and nine branch libraries. You work in the Information Technology department for the county and are developing an Access database application to help manage the employees who work for the library system. Open the Branches table in Design View, and then modify the table as follows:

a. After the Street field, add a new field named Website with the Hyperlink data type.

b. Use BranchID to rename the ID field. Save and close the Branches table.

2. Open the Positions table in Datasheet View, and then complete the following tasks:

a. Delete the record for Library Intern (PositionID 5).

b. Edit Children's Librarian to be Youth Librarian for PositionID 7.

c. Enter a new record into the table using the information in Table 1. Close the Positions table.

Table 1: New Record for the Positions Table

PositionID | 18 Title | Archivist Description | Manages historical documents and materials. MinQualifications | BA

3. In the Navigation Pane, make Facilities the new name for the Locations table.

4. Use the Import Spreadsheet Wizard to import the data from the Support_AC19_CS1-4b_Locations.xlsx file, and then append the data to the Facilities table. Do not save the Import steps. (Hint: If a warning message appears, click Open.)

5. Create a new table in Design View with the following options:

a. Add a new field named PTEmpID with the AutoNumber data type. Set it as the primary key field.

b. Add two new fields named PTFirst and PTLast in that order. They should have the Short Text data type and field size properties of 25.

c. Add a new field named Email with the Hyperlink data type. Set the Required property to Yes.

d. Add a new field named HourlyRate with the Currency data type. Set the Default Value property to 15.

e. Save the table using PartTimeEmployees as the table name.

f. Add a new field named CellPhone with the Short Text data type. Save the table again, and then update the Input Mask property using the Input Mask Wizard. Select the Phone input mask, and then update the field to !(999) 000-0000 so that !\(999") "000\-0000;;_ is the final input. Accept the default in the last window. Save and close the table.

6. Open the Employees table in Datasheet View, and then complete the following tasks:

a. Delete the SocSecNumber field. (Hint: It currently does not contain any data.)

b. Move the Position field to be the last field in the datasheet.

c. Sort the records in ascending order by the LastName field. Save and close the Employees table.

7. Using the Employees and Positions tables, create a new query in Design View with the following options:

a. Select the FirstName, LastName, and Salary fields from the Employees table and the Title and MinQualifications fields from the Positions table.

b. Join the tables using the Position field from the Employees table and the PositionID field from the Positions table.

c. Save the query using LowSalaries as the name.

8. With the LowSalaries query still open in Design View, add criteria to select only those records where the salary is less than or equal to 30000. Save the query, switch to Datasheet View, as shown in Figure 1, and then close it.

Figure 1: Final LowSalaries Query in Datasheet View

9. Use the Simple Query Wizard to create a new query with the following options:

a. Use the Employees and the Branches tables.

b. Select the FirstName, LastName, and HireDate fields from the Employees table and the BranchName field from the Branches table.

c. Use HireDates as the title for the query. Save and close the query.

10. Open the PositionListing query in Design View, and then modify it as follows:

a. Add criteria to select all records with Digital Librarian or Web Content Specialist as the title.

b. Sort the records in ascending order first by the LastName field, and then by the FirstName field. Run the query to display it in Datasheet View, as shown in Figure 2, and then save and close it.

Figure 2: Final PositionListing Query in Datasheet View

11. Open the LibraryTitleDate query in Design View, and then add criteria using wildcard characters to select all records with Library anywhere in the Title field and a HireDate value greater than or equal to 1/1/2016. Run the query to display it in Datasheet View, as shown in Figure 3, and then save and close it.

Figure 3: Final LibraryTitleDate Query in Datasheet View

12. Use the Form Wizard to create a new form with the following options:

a. Select all of the fields from the Positions table.

b. Apply the Columnar layout.

c. Use PositionsEntry as the form title. Save and close the PositionsEntry form.

13. Use the Report Wizard to create a new report with the following options:

a. Include the FirstName, LastName, BranchID, Position, and HireDate fields from the Employees table.

b. Group the records by the BranchID field.

c. Sort the records in ascending order by the Position field.

d. Apply the Stepped layout and the Landscape orientation.

e. Use BranchHireListing as the report title. Preview, save, and then close the report.

14. Open the JobApplicantEntry form in Form View, enter Jason as the FirstName value for the first record to replace "Jared" and then add a new record to the form with the information in Table 2. Close the form.

Table 2: New Record for JobApplicantEntry Form

AppID | (Autonumber field will automatically increment) LastName | Stedman FirstName | Elizabeth

15. Open the EmployeeSalaryInfo form in Layout View, and then modify it as follows:

a. Right-align the text in the EmpID, FirstName, LastName, HireDate, and Salary labels.

b. Change the FirstName label to First Name, change the LastName label to Last Name, and then change, and use Hire Date to rename the HireDate label.

c. Delete the Close command button in the Form Footer section. Save the form, but do not close it.

16. With the EmployeeSalaryInfo form still open in Layout View, modify it as follows:

a. Add the Phone field to the form, and then position it just below the Salary label.

b. Change the font color for the Phone label to Automatic (black).

c. Apply quick style, Colored Outline - Orange, Accent 2 (1st row, 3rd column in the Theme Styles gallery) to the Close command button in the Form Header section. Display the form in Form View, as shown in Figure 4, and then save and close it.

Figure 4: Final EmployeeSalaryInfo Form in Form View

17. Open the BranchListing report in Layout View, and then add conditional formatting so that any Salary field value greater than or equal to $50,000 is formatted using the Green 3 background color (7th column, 4th row in the Standard Colors palette). Save the BranchListing report.

18. With the BranchListing report still open in Layout View, modify it as follows:

a. Change the background color of the Salary label in the Page Header section to Green 3 (7th column, 4th row in the Standard Colors palette).

b. Group the two labels in the Report Header section together in a tabular layout. Save and display the report in Report View, as shown in Figure 5, and then close it.

Figure 5: Final BranchListing Report in Report View

19. Open the EmployeeContactListing report in Layout View, and then modify it as follows:

a. Change the theme colors to Green.

b. Change the theme fonts to Franklin Gothic. Display the report in Report View, as shown in Figure 6, and then save and close it.

Figure 6: Final EmployeeContactListing Report in Report View

Save and close any open objects in your database. Compact and repair your database, close it, and then exit Access. Follow the directions on the SAM website to submit your completed project.