New Perspectives Access 365 | Module 3: SAM Project A Lighthouse Cybersecurity

GETTING STARTED

1. Save the file NP_AC365_3A_FirstLastName_1.accdb as NP_AC365_3A_FirstLastName_2.accdb

a. Edit the file name by changing “1” to “2”.

b. If you do not see the .accdb file extension, do not type it. The file extension will be added for you automatically.

2. With the file NP_AC365_3A_FirstLastName_2.accdb open, ensure that your first and last name is displayed as the first record in the _GradingInfoTable table.

a. If the table does not display your name, delete the file and download a new copy.

PROJECT STEPS

1. You are a project assistant for Lighthouse Cybersecurity, a consulting firm that provides information security services throughout the United States. You need to query the database to help make decisions and answer questions from company managers. Start by creating a query in Design View that lists analysts by their last name and includes the following options:

a. Add the Analyst table to the query window.

b. Add the AnalystID, LastName, and FirstName fields to the design grid in that order.

c. Sort the records in ascending order by LastName to list the analysts alphabetically by their last name.

d. Save the query using LastNameSorted as the query name. Open the query in Datasheet View and then close it.

2. Open the RegionCriteria query in Design View and make the following changes to select records only for analysts in the northwest:

a. Delete the AnalystID column from the design grid.

b. Add criteria to select only those records where NW is the Region field value.

c. Save the changes to the RegionCriteria query. Open the query in Datasheet View and then close it.

3. Open the PaidOrBalance query in Design View and make the following changes to select records only for clients who have not made a payment or whose balance is $0.00:

a. Add criteria to select only those records where the Paid field value equals 0 or the Balance field value equals 0.

b. Save the changes to the PaidOrBalance query. Open the query in Datasheet View, confirm that four records appear in the PaidOrBalance query results, and then close the query.

4. Open the SalaryComparison query in Design View and make the following changes to use the analysts' full names, select records for analysts with salaries over $70,000, and correct an error:

a. Add the FirstName field to the query design grid. The FirstName field should immediately follow the AnalystID field.

b. Add criteria to select only those records where the Salary field value is greater than 70,000.

c. Sort the records by Salary in descending order.

d. Run the query.

e. With the query open in Datasheet View, edit the first record by changing the FirstName value to Mariana from Maria. Confirm that the query results match Figure 1. Save the query again and close it.

Figure 1: SalaryComparison Query Results

5. Open the RegionLastName query in Design View and make the following changes to display different fields and sort them first by region and then last name:

a. Delete the AnalystID field from the query grid.

b. Move the Region field to the beginning of the design grid.

c. Add the LastName field to the design grid so that the order of the fields in the grid is Region, LastName, FirstName, StartDate.

d. Sort the records in ascending order by the Region field and in ascending order by the LastName field. Save the changes to the query. Open the query in Datasheet View and confirm that it matches Figure 2. Close the query.

Figure 2: RegionLastName Query Results

6. Open the PaidAndBalance query in Design View and make the following changes to select records for clients who have not made a payment and have a balance of $0.00:

a. Add criteria to select only those records where the Paid field value equals 0 and the Balance field value equals 0.

b. Save the changes to the query. Open the PaidAndBalance query in Datasheet View, confirm that one record appears in the query results, and then close the query.

7. Open the StartDates query in Design View and make the following changes to select records only for analysts who started working after January 1, 2028:

a. Add criteria to select only those records where the StartDate field value is greater than 1/1/2028.

b. Save the changes to the query. Open the query in Datasheet View, confirm that 15 records appear in the query results, and then close the query.

8. Create a query in Design View that lists analysts and their region by name and includes the following options:

a. Add the Analyst table and the Region table to the design window.

b. Add the FirstName and LastName fields from the Analyst table to the design grid.

c. Add the RegionName field from the Region table to the design grid.

d. Join the Analyst table and the Region table using the Region field in the Analyst table and the RegionCode field in the Region table.

e. Save the query, using AnalystsByRegion as the name. Open the query in Datasheet View and then close it.

9. Open the ProjectWeeks query in Design View and make the following changes to select records only for project types estimated to require four weeks:

a. Add criteria to select only those records where 4 is the Weeks field value.

b. Save the changes to the query. Open the query in Datasheet View, confirm that four records appear in the query results, and then close the query.

10. Open the EstMaxWeeks query in Design View and make the following changes to calculate the maximum number of weeks required for each project type:

a. Create a calculated field by entering MaxWeeks: [Weeks] + 4 in the Zoom dialog box of the first empty column in the design grid.

b. Save the query. Open the query in Datasheet View and then close the query.

11. Create a query in Design View that sums the analyst salaries by region and includes the following options:

a. Add the Analyst table to the design window.

b. Add the Region and Salary fields to the design grid in that order.

c. Display the Total row in the design grid.

d. Group the records by the Region field and sum the values in the Salary field.

e. Sort the records in ascending order by the Region field.

f. Change the properties of the Salary field to use the Currency format and 0 decimal places.

g. Save the query, using TotalSalaryByRegion as the name. Open the query in Datasheet View, resize the SumOfSalary column to its best fit, and then save and close the query.

12. Open the Analyst table in Datasheet View to correct an error. Use the Find and Replace feature to find the analyst whose first name is Mai and replace the name, using Maya as the new value. Close the Analyst table.

13. Open the Region table in Datasheet View and make the following changes to improve its appearance:

a. Change the font to Arial.

b. Change the font size to 12.

c. Resize all columns their best fit.

d. Change the alternate row color to Blue, Accent 1, Lighter 80% (5th column, 2nd row in the Theme Colors palette). Save the changes to the table and close it.

14. Open the Analyst table in Datasheet View and hide the Region and Salary columns. Save the changes to the table and close it.

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 website to submit your completed project.