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

GETTING STARTED

1. Save the file NP_AC365_5A_FirstLastName_1.accdb as NP_AC365_5A_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_5A_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 work as a project assistant for Lighthouse Cybersecurity, an information security consulting company in Tacoma, Washington. For high-level planning purposes, you have created an Access database to track new clients and their projects, services, and analysts. To provide accurate information to other employees, you will improve the tables and queries of the database. In Design View of the Client table, add three new fields with the following specifications to track additional client information:

a. Add a field named Website with a Hyperlink data type.

b. Add a field named Logo with an Attachment data type.

c. Add a field named Comments with a Long Text data type. For the Comments field, use Yes as the value for the Append Only property value to track the changes to the field. Save the Client table.

2. While still in Design View of the Client table, modify the following field properties to make the fields easier to use:

a. Use 30 as the Field Size property for the ClientName field.

b. Use Client as the Caption property for the ClientName field.

c. Choose Medium Date as the Format property for the ProjectStart field. Save the Client table. If prompted that some data may be lost, click Yes to continue. (Hint: No data is lost because no values exceed 30 characters in the ClientName field.)

3. While still in Design View of the Client table, modify the following field properties to increase the accuracy of data entry:

a. Choose Yes as the Required property for the ServiceCode field.

b. Enter =1/1/2029 as the Validation Rule property for the ProjectStart field.

c. Enter Must be later than 1/1/2029 for the Validation Text property for the ProjectStart field. Save the Client table. If prompted to test data integrity rules, click Yes.

4. While still in Design View of the Client table, add a new field immediately below the existing fields using the Lookup Wizard and the following information to store the geographical region for each client:

a. Enter RegionCode as the field name.

b. Get the values from the Region table.

c. Select the RegionCode and RegionName fields.

d. Sort the records in ascending order by the RegionName field.

e. Do not hide the key column.

f. Store the RegionCode value, use the other default options of the Lookup Wizard, and save the Client table as prompted.

g. Use Yes for the Column Heads Lookup property for new RegionCode field. Save the Client table.

5. Switch to Datasheet View of the Client table, and then select NE (Northeast) as the RegionCode value for the first record (ClientID 1, Astrum Logistics).

6. Switch to Design View of the Client table and modify the following table properties to further increase accuracy in data entry:

a. Add [ProjectStart]=[ContractSigned] as the Validation Rule for the table.

b. Enter Contract must be signed before the project start date as the Validation Text property for the table. Save and close the Client table. Click Yes if prompted to test for data integrity.

7. Open the Analyst table in Datasheet View and modify the datasheet as follows to summarize analyst data:

a. Add a Total row to the datasheet.

b. Sum the values in the Salary field.

c. Resize the Salary field to display the new total. Save and close the Analyst table.

8. In Design View of the ProjectsByClient query, modify the query as follows to make the datasheet easier to interpret and to summarize project data:

a. Use Project as the Caption property for the Description field.

b. Use Clients as the Caption property for the ClientName field.

c. Use Total Fee as the Caption property for the MonthlyFee field.

d. Add the Total row to the query grid, count the ClientName field, and sum the MonthlyFee field.

e. Save and view the query in Datasheet View, add a Total row to the datasheet, and then sum the Clients and Total Fee columns.

f. Resize the Total Fee column to its best fit as shown in Figure 1. Save and close the ProjectsByClient query.

Figure 1: Final ProjectsByClient Query in Datasheet View

9. Create a new query in Query Design View based on the Analyst table as follows to select the top five records depending on a maximum salary amount:

a. Add the FirstName, LastName, and Salary fields in that order.

b. Sort the records in descending order on the Salary field.

c. Return the top 5 records.

d. Add [Enter maximum salary] as a parameter prompt for the Salary field.

e. Save the query and use SalaryParameter as the query name.

f. Run the query using 70000 as the parameter prompt to produce the results shown in Figure 2. Close the SalaryParameter query.

Figure 2: Final SalaryParameter Query in Datasheet View

10. Create a new parameter query in Query Design View based on the Analyst table as follows to select records for analysts depending on the first two letters in their last names:

a. Add the AnalystID, FirstName, LastName, and Region fields to the query grid in that order.

b. Use the Zoom dialog box to enter the following criterion for the LastName field: Like [Enter first two letters of analyst last name] & "*"

c. Save the query using AnalystParameter as the name and run the query to test it. Enter Ha in the parameter prompt to return three records. Close the query.

11. Use a query wizard to create a crosstab query based on the ClientsByService query as follows to list the number of clients per service and project:

a. Add the ServiceName field as the Row Heading field.

b. Add the ProjectCode as the Column Heading field.

c. Count the ClientName field as the Value field. Save the query with the default name ClientsByService_Crosstab and close the query.

12. Create a new query in Query Design View based on the Analyst table as follows to display the average salary of analysts in the United States:

a. Add the Region and Salary fields in that order.

b. Add the Total row to the query grid and average the Salary field.

c. Use the Not keyword to add a criterion to the Region field to return analysts located outside of CN (Canada).

d. Change the format of the Salary field to the Currency format.

e. Save the query and use USAvgSalary as the query name.

f. Run the USAvgSalary query and then close it.

13. Create a new query in Query Design View as follows to display clients with specified project types in the technology industry:

a. Add the Client and Project tables.

b. Add the ClientName, Industry, and ProjectStart fields from the Client table and the ProjectCode field from the Project table.

c. Set the criterion for the ProjectCode field using the In keyword to include only AppSec, EndSec, and MobSec as project codes.

d. Using the Expression Builder, create a new field using IsTech as the name.

e. In the Expression Builder, use the IIf function to populate the IsTech field with "Yes" if the industry is "Tech" and "No" if not.

f. Save the query as TechClients and run the query. Close the TechClients query.

14. Use the Find Unmatched Query Wizard as follows to find records in the Analyst table that contain no related records in the Project table:

a. The ProjectID field in each table contains matching data.

b. Include the AnalystID, FirstName, and LastName fields in the query results.

c. Use AnalystsWithoutProjects as the query name, and view the results in Query Datasheet View as shown in Figure 3.

Figure 3: Final AnalystsWithoutProjects Query in Datasheet 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 website to submit your completed project.