Computer Apps 1

1. Computer Apps 1 Queries-part 2 5.02b

2. Queries • Query – a database object that requests information from a database and creates a dataset (list of selected records) matching the requested information • Query Design View – a window that allows one to choose the fields in a query, to sort, and to set criteria. • Query Design Grid – the lower half of the Query Design view that shows the field name, table name, sort order, show box, and criteria rows for selecting records.

3. Queries – Questions • Queries are like advanced filters except a query can be saved and stored in the database window. • Queries also perform specialized operations like performing calculations on field values and placing the results in a new column in a datasheet. • Similar to advanced filters, queries allow sorting and defining of criteria to select the records desired.

4. Selecting Fields in a Query Choose the fields from the table or from the grid below.

5. Select Fields and Set Criteria Step 1: Select queries. Step 2: Select Create query. Step 3: Select fields; Set criteria and/orsorting requirements. Step 4: Run the query. 2 1 3

6. Query Results Records displayed included: *Genre = “Classical” *Artist name sorted in ascending order *CD Title

7. Build a Query Query: A database object that allows the user to select records/data from a database using multiple criteria (comparison operators) and arrangement (sorting) standards. • Advantages • Queries can be saved • Each table can be associated with multiple queries

8. Set Conditions for Query Criteria: ‘Release Date’ > ‘7/01/2004’ Field Affected Sort Order

9. Comparison Operators for Queries and Filters • Equals = • Does not equal <> • Between and between and • Less than < • Greater than > • Equal to or less than <= • Equal to or greater than >=

10. Query: Advanced Criteria • AND Condition: Uses the same row in the Query Design window • The AND condition reduces the number of records because both conditions must be met. • OR Condition: Uses different rows in the Query Design window • The OR condition increases the number of records because records are selected if either condition is met.

11. Query: AND Condition Results Criteria: Genre = “Rock” AND Release Date after 2002 AND condition listed on ONE line

12. Query: OR Condition Results OR Condition takes 2 lines! Genre = Country OR Rock And Release Date Greater than 01/01/2000

13. Search Records • To search for specific records, select Find command from the Edit menu. • Key the search string (data) in the dialog box. • Key “where” to find the records (which table). • Similar to Find/Replace commands in other programs • Not case sensitive

14. Printing Tables – Portrait View Always check printouts using PRINT PREVIEW before printing. Use the menu bar and select File/Page, Setup/Page to change the orientation. Portrait - Taller than wide view

15. Printing Tables – Landscape View Landscape – Wider than tall view

16. Printing Forms Remember to choose the “Selected Record(s)” option in the Print Dialog Box when printing one or selected forms. The default will print forms for all records in the database.