150 likes | 331 Views
Creating Queries. 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. Different Types of Queries. Select Query Simple Query Wizard
E N D
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
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
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
Design Grid of a Select Query The question is asked using a design grid. Each field has its own column. Prof. Leighton
The Dynaset The answer is displayed in a dynamic subset of a table, a Dynaset. Prof. Leighton
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
Selection Criteria Prof. Leighton
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
Comparison Operators Prof. Leighton
More Comparison Operators Prof. Leighton
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
Multiple Conditions Prof. Leighton
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
Action Query Queries Available Prof. Leighton