Creating Queries

1. Creating Queries

2. Extracting Information • Display selected fields and records from a table • Sort records • Perform calculations • Generate data for forms, reports and other queries • Access data from two or more tables Prof. Leighton

3. Different Types of Queries • Select Query • Simple Query Wizard • Crosstab Query • Similar to Pivot table in Excel • Find Duplicates Query Wizard • Find Unmatched Query Wizard Prof. Leighton

4. Select Query • Use Access’s select query design window • We create an example of the data we need • The example is created in a design grid • Access then seeks all records that match our example • This approach is called query by example or QBE • The query returns an answer in a dynaset • Same format as a table in the datasheet view Prof. Leighton

5. Design Grid of a Select Query The question is asked using a design grid. Each field has its own column. Prof. Leighton

6. The Dynaset The answer is displayed in a dynamic subset of a table, a Dynaset. Prof. Leighton

7. Selection Criteria • A condition is a criterion for selecting records • Place the condition in the Criteria text box (the criteria row) for that field • To form a simple condition, you enter a comparison operator and a value Prof. Leighton

8. Selection Criteria Prof. Leighton

9. Criteria • Data type determines how criteria are specified • Access converts data to standard format • Text fields are enclosed in quotations • Number, currency and counter fields • Enter digits without commas and w/o dollar signs • Decimals are allowed • Dates are enclosed in pound signs • mm/dd/yy form • Yes/No fields • Entered as Yes or No Prof. Leighton

10. Comparison Operators Prof. Leighton

11. More Comparison Operators Prof. Leighton

12. Multiple Conditions • Require logical operators to combine two or more simple conditions • The and logical operator implies that both conditions are met • Place both conditions in the same Criteria row • The or logical operator implies that one or the other condition is met, or both • Place the conditions in different criteria rows Prof. Leighton

13. Multiple Conditions Prof. Leighton

14. Action Queries: Modify the Database • Update query • Changes multiple records within a table • Append query • Adds records from one table to another • Delete query • Deletes one or more records from a table • Make-Table query • Creates a new table from records in an existing table Prof. Leighton

15. Action Query Queries Available Prof. Leighton