Access 2016 Intermediate

Lesson 4 – Designing Advanced Queries

Access 2016

E XERCISE

D ESIGNING A DVANCED Q UERIES



Task

Design advanced queries.

1. Open ADVQUE1X.ACCDB . 2. Open the Project Sales query in Design view.

3. Create a new calculated field named Total Sales that multiplies the number of students to be trained ( Students field in the Project table) by the per student cost of the course ( Cost field in the Courses table). ( Hint: Use the format Total Sales:[students]*[cost] .) 4. Run the query to view the results. Close the query and save it. 5. Open the Trainer Pay query in Design view. ( Hint: Right Click and select Design View 6. Create a new field that concatenates the first and last names of each trainer. Name the field Trainer Name and use the Last Name, First Name format. ( Hint: Use the format Trainer Name:[last name]&", "&[first name] .) 7. Run the query and widen the Trainer Name column to view the text. Then, return to Design view. 8. Trainers are paid fifteen percent of the total sale for each project. Create a calculated field named Pay that calculates the trainer's pay for each project. ( Hint: Use the format Pay:[students]*[cost]*.15 .) 9. Format the Pay field as currency. Run the query to view the results. Then, return to Design view. 10. Find the three trainers with the highest pay. ( Hint: Sort the Pay field in Descending order and type 3 into the Return box on the toolbar.) Run the query to view the results. Then, return to Design view. 11. Reset the Pay field back to (not sorted) and the top values back to All ; then, remove the Total row. ( Hint: Use the Totals button on the Query Design toolbar.) 12. Run the query again. Use the Filter by Selection button to display only those records for George Edwards. 13. Close the query, saving the changes. 14. Open the Sales by Rep query in Design view. Add a parameter to the Criteria row of the Sales Rep field that prompts you to enter the initials of the sales representative each time the query is run. ( Hint: Remember to use both opening and closing brackets.)

Page 86

OFFICEPRO, Inc.