Access Query Design

1. Access Query Design

2. Access Query Design The Query design screen provides a mechanism for selecting specific data from datafile(s) by: • displaying only selected fields • (Can also change the order of fields) • only records that match given criteria • reorder records • can also link across multiple files IT Fundamentals

3. Access Query Design • Open Access Query Screen • Open the Database • Click “Queries” • Choose “Create query in Design View” • Click ”Design” • choose tables to add • Click “close” IT Fundamentals

4. Access Query Screen Design screen: contains top section which shows the tables to be used in the query Bottom section: contains fields and criteria used in the query IT Fundamentals

5. Access Query Screen (cont.) Selecting Fields: Highlight the field in the active table. (Double Click) Drag the field from the active table and drop it in the field view skeleton. (Choose) Selecting Records: CRITERIA Enter criteria into bottom section of the query design screen. Character field: Data is entered into the file skeleton as is or encased in quotes. Can also retrieve a range of values: eg. < "M" IT Fundamentals

6. CRITERIA Retrieve all employees who are in Back department IT Fundamentals

7. CRITERIA (CONT.) Numeric field: eg. Retrieve all records with pay_rate <= 8.50 IT Fundamentals

8. CRITERIA (CONT.) Date Fields: eg. before 27th July 1987 is entered as <27/07/87 IT Fundamentals

9. CRITERIA (CONT.) Logical Fields: Enter Yes or No Note: NO quote marks (“) – “Yes” (WRONG), Yes (CORRECT) Retrieve all employees who are union members IT Fundamentals

10. MULTIPLE CRITERIA Condition1 AND Condition2 Enter conditions on the same line. eg. Department=“Back" AND Pay_Rate > 7.00 IT Fundamentals

11. MULTIPLE CRITERIA (CONT.) Output: IT Fundamentals

12. MULTIPLE CRITERIA (CONT.) eg2. Pay_Rate between 7.00 AND 9.50 IT Fundamentals

13. MULTIPLE CRITERIA (CONT.) Output: IT Fundamentals

14. MULTIPLE CRITERIA (CONT.) Condition1 OR Condition2 Enter conditions on different lines in file skeleton: Eg. Department is either “Back" OR "Centre" IT Fundamentals

15. MULTIPLE CRITERIA (CONT.) Output: IT Fundamentals

16. MULTIPLE CRITERIA (CONT.) Eg. 2 Department is either “Back” OR Pay_rate >9.00 IT Fundamentals

17. MULTIPLE CRITERIA (CONT.) Output: IT Fundamentals

18. SORTING RECORDS Access allows you to sort the output in ascending or descending order on character, numeric & date fields. IT Fundamentals

19. SORTING RECORDS (CONT.) Example 1: Sort all employees in ascending order according to Name. Show Name, DOB & Department IT Fundamentals

20. SORTING RECORDS (CONT.) Output: IT Fundamentals

21. SORTING RECORDS (CONT.) Example 2: Sort all employees in ascending order according to Name within each Department. Show Name, DOB & Department IT Fundamentals

22. SORTING RECORDS (CONT.) Output: IT Fundamentals

23. SHOWING RECORDS Tick the SHOW box if you want the field to be displayed. Note: Not all SHOW boxes should be ticked. IT Fundamentals

24. SORTING RECORDS (CONT.) Output: IT Fundamentals

25. CALCULATION FIELDS Retrieve all employees whose Salary exceed 250. Here the Salary = Pay_Rate*HOURS_WORK. Note:In the Field row of a new column enter: Salary: PAY_RATE*HOURS_WORK Or: Right-Click the Field row of a new column choose “Build” Open an Expression Builder window IT Fundamentals

26. Expression Builder window IT Fundamentals

27. In the query design view, rename “Expr1” as “Salary” IT Fundamentals

28. CALCULATION FIELDS Output: IT Fundamentals

29. LAB 6 • Checking you Data file • Make sure they are the same as in Page93 • Do Lab6 “Access Queries” on Page94~99 • Save your result in “c:\homework” IT Fundamentals