1 / 15

Creating Queries

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

dusan
Download Presentation

Creating Queries

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related