Access: Queries

1. Access: Queries Chapters A1-A3 All Sections

2. Today’s Topic Security Hardware & Software IS Basics Information Quality Types of IS Telecommu-nications Systems Development PowerPoint Functional, Crossfunctional & Interorganizational Database Processing & Design Spreadsheet Design E-commerce Access Excel Business Intelligence

3. Access Queries Access Properties Queries Sorting Calculations Selection Criteria

4. Objective • State the purpose of common table properties. • Create queries to support common business problems. (Using Query Design View only) • Distinguish between record and summary calculations.

5. What are Access Objects? • Tables • Queries • Reports • Forms

6. What Table Properties Can I Set? • Field Name • Data Type • Field Size • Format (output) • Decimal Places • Input Mask • Default Value • Validation Rule • Required • Indexed

7. What is the Purpose of Queries? • Ad-hoc information retrieval • Output subsets of data

8. Example Data Participant Registration Card Participant ID: 1 Age: Gender: Male Female Are you married? Y or N Are you a parent? Y or N Are you a home owner? Y or N What is your favorite food?

9. Example Data Observation Card Participant ID #: Observation Date: 1 = Poor … 5 = Excellent Rating of Product A: 1 2 3 4 5 Rating of Product B: 1 2 3 4 5 Rating of Product C: 1 2 3 4 5 Rating of Product D: 1 2 3 4 5

10. Open Existing Database Start Access

11. Review Tables

12. Start a Query Example: List demographics of all participants.

13. Fields

14. Run a Query

15. Show & Hide Fields Example: List demographics of all participants but hide homeowner field.

16. Save a Query

17. Sort Records Example: List ratings for Product A from high to low.

18. Select Records Example: List demographics of Female participants.

19. Query More Than One Table Example: List all observation ratings by female participants.

20. Selection Criteria: Operators Example: List demographics of all participants over 30 years of age.

21. Selection Criteria: NOT Example: List demographics of all participants who are not female.

22. Selection Criteria: * and ? Example: List demographics of all participants who like liver. Liver may be anywhere in the Favorite Food string; beginning, middle or end.

23. Selection Criteria: NULL Example: List demographics of all participants who did not identify a favorite food.

24. Selection Criteria: AND Example: List demographics of female participants who are over thirty.

25. Selection Criteria: OR Example: List demographics of participants who are either Female or over 30.

26. Combine Selection Criteria Example: List all female participants who are married and all male participants who are parents.

27. Selection Criteria: Dates Example: List all observations recorded on Feb 24, 2011.

28. Summary Calculations One calculation total for a group of records. Example: Calculate the average rating each participant assigned to Product A.

29. Record Calculations One calculation per record. Example: Determine the age of each participant in five years.

30. Query Limitations • Queries can show only one level of grouping at a time. • Queries have limited formatting options.