116 completed insuranceagentsbranchparameter query change back O t h e r

This file has the instruction given and has to be done within the requirement:

1. Open the CentralSierra-03.accdb database start file.
2. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor
3. Enable the contents in the security warning.
4. Run the NewSalaryCostSummary query. The results display in Datasheet view (Figure 3-111). The query is supposed to summarize results by department and, within the department by title, the total salary cost and the total cost of potentially giving a 3% raise to all employees. Several errors need to be repaired.
   

   

5. Edit the query in Design view to remove the unneeded columns that cause the summary query to display too many records.
   1. Delete the FirstName column.
   2. Delete the LastName column.
   3. Save and run the query to test the results. The datasheet should now show only 14 records.
6. Edit the query in Design view to repair the calculated field name and formatting.
   1. Open the Zoom box for the Expr1 field to change the default Expr1 name to SumOfPotentialNewSalary. Do not change the existing formula.
      

      

   2. Open the Property Sheet for this field and change the Format property to Currency.
   3. Close the Property Sheet.
   4. Save and run the query to test the results. The datasheet should show the improved calculated field (Figure 3-112).
7. Edit the query in Design view to fix the grouping order.
   

   

   The results are supposed to display the records grouped by the department, and then within each department by the job titles.

   1. Move the Department field to the left of the Title field.
   2. Save and run the query to test the results. The datasheet should show the records with the correct grouping (Figure 3-113).
8. Edit the query in Design view to enhance the functionality and improve the appearance. In addition to calculating the sum of both the Base Salary and the Potential New Salary fields, management would like to see the average of both of those fields, as well as the total number of employees in each area.
   1. Insert a new column to the left of the BaseMonthlySalary column.
   2. Click the drop-down arrow in the Field row of this new column and select EmpID. Set this field to use the Count aggregate function.
   3. Add the BaseMonthlySalary field into the first empty cell in the Field row. BaseMonthlySalary will be in the query design grid twice. Set this field to use the Avg aggregate function.
   4. Type the following formula into the first empty cell in the Field row: AvgOfPotentialNewSalary: Avg([BaseMonthlySalary]*1.03) .
   5. Set the Total row cell for this field to Expression.
   6. Format this field to use Currency.
   7. Save and run the query to test the results. Adjust the column widths as necessary to improve the appearance. The datasheet should now be similar to that shown in Figure 3-114.
      

      

9. Save any changes and close the query.
   

   

10. Run the InsuranceAgentsBranchParameter query. Enter Folsom in the Enter Parameter Value dialog box. The results display in datasheet view (Figure 3-115). The query is supposed to list the insurance agents in a particular branch or show all the insurance agents if the branch prompt is left blank. You have identified several errors that need to be fixed, including that all the insurance agents displayed even though you entered a branch.
11. Edit the query to fix the parameter problem.
    1. Open the query in Design view. Notice that the branch parameter is actually on the Department field and not the Branch field.
    2. Change that column to contain the Branch field.
    3. Run the query again and enter Folsom. The results now display the branch, but you still have employees from all the branches.
    4. Switch to Design view to review the criteria. The Branch parameter is on the “or” row. It needs to be on the same row as “Insurance Agent”.
    5. Move the parameter to the criteria row to fix that problem.
    6. Open the Zoom window to review the Branch parameter. It isn’t designed correctly, because it won’t allow the field to be left blank and show all the insurance agents.
    7. Edit the parameter as follows: Like [Enter the branch or leave blank to select all branches] & “*”.
    8. Save and run the query. Type Folsom. Only the four agents at the Folsom branch should display.
       

       

    9. Change back to Design view and then run the query again and leave the dialog box empty. All 14 agents should display.
12. Edit the query to fix the Employee field. Notice that there isn’t a space between the names.
    1. Edit the formula in the Employee field as follows: Employee: [FirstName] & ” ” & [LastName] .
    2. Run the query. Enter Granite Bay. The results should show only the five insurance agents at the Granite Bay branch.
    3. Use AutoFit to adjust the width of all the columns. The datasheet should now be similar to that shown in Figure 3-116.
13. Save and close the query.
14. Close the database.
15. Upload and save your project file.
16. Submit project for grading.