QUERYING A DATABASE

1. QUERYING A DATABASE By: Dr.Ennis-Cole

2. OBJECTIVES: • Learn how to use the Query window in Design view • Create, run and Save queries • Define a relationship between two tables • Sort data in a query • Filter data in a query • Specify an exact match condition in a query

3. OBJECTIVES: • Change a datasheet’s appearance • Use a comparison operator to match a range of values • Use the AND and OR logical operators • Perform the calculations in a query using calculated fields, aggregate functions, and record group calculations

4. QUERIES: An INTRODUCTION • A query is a question you ask about stored data • Access provides the following capabilities: • Display selected fields and records from a table • Sort records • Perform calculations • Generate data for forms, reports and other queries • Update data in the tables of a database • Find and display data from two or more tables

5. QUERY WINDOW • You use the Query window to create a Query in Design view • By using QBE, you give Access an example of the information you are requesting • Access then retrieves the information that precisely matches your example • Refer to Figures 3-1 and 3-2

6. CREATING AND RUNNING A QUERY • Drag CustomerNum from the Customer field list • Double click CustomerName • Repeat the steps for the City, OwnerName and First contact fields • Click the Run button • Save, type a name and close the window

7. DEFINING TABLE RELATIONSHIPS • Define relationships between tables • Use a common field to relate two tables • This process is called a JOIN • When you join tables that have a common field, you can extract data from them as if they were a single large table

8. ONE-TO-MANY RELATIONSHIPS: • Exists when one record in the first table matches zero, one or many records in the second table, or when one record in the second table matches exactly one record in the first table • Access refers to the two tables that form a relationship as the primary table and the related table

9. ONE-to-MANY RELATIONSHIPS: • Primary table: “One” table in a 1:M relation • Related table:“Many” table in a1:M relation • Because a related table is stored in two tables, inconsistencies between the tables can occur • There are 3 major anomalies: • Addition • Deletion & • Update anomalies

10. REFERENTIAL INTEGRITY • Is a set of rules that Access enforces to maintain consistency between related tables • Rules of RI: • When you add a record to a related table, a matching record must already exist in the primary table • If you attempt to change the value of the primary key in the primary table, Access prevents this change • When you delete a record in the primary table, Access prevents the deletion if matching records exist in a related table

11. DEFINING A RELATIONSHIP • When two tables have a common field, you can define a relationship between them in the relationships window • In this window, you can view or change existing relationships, define and rearrange the table layout • Refer to Figures 3-6 & 3-7

12. SORTING DATA IN A QUERY • Is the process of rearranging records in a specified order • Often you need to sort before displaying it • When you sort, you do not change the sequence of the records in the underlying tables • Only the records in the query are rearranged

13. SORTING DATA IN A QUERY • To sort, you must select the Sort key • Sort keys can be text, number, date/time, Currency, AutoNumber, Yes/No • They are not memo, OLE object or hyperlink objects • You sort keys in either ascending or descending order • Click the toolbar sort buttons to sort quickly

14. SORTING MULTIPLE FIELDS IN DESIGN VIEW • Sort keys can be unique or non-unique • When the sort key is non-unique, records with the same sort key value are grouped together • But, they are not in a specific order within the group • To arrange these, you can specify a Secondary Key

15. SORTING A QUERY DATASHEET • The first sort key field is called the primary sort key • A table has at most one primary key, which must be unique • Any field in a table can serve as aprimary sort key • Access lets you select up to 10 different sort keys

16. SORTING & FILTERING DATA • In the Query datasheet, select the field or adjacent fields on which you want to sort • Click the Sort Ascending button or the Sort Descending button on the Query Datasheet toolbar • A Filter is a set of restrictions you place on the records in an open datasheet or form to temporarily isolate a subset of the records • In the datasheet, select all for filter • Click the Filter By Selection button the toolbar

17. DEFINING RECORD SELECTION CRITERIA FOR QUERIES • Just as you can display selected fields, you can display selected records • You must specify a condition as part of the query • A condition is a criteria that determines which records are selected • It consists of an operator and a value • It could be a comparison operator or an exact match • Refer to Figures 3-17 through 3-20

18. CHANGING A DATASHEET’S APPEARANCE • You can change characteristics of a datasheet • Click the Maximize button • Click the record selector to select the entire sheet • Click Format, Font • Scroll, Click 8, Click OK button • Press and hold the shift key, then click the mouse button, all columns are selected • Double-click the mouse button, all columns are resized to their best fit • Click Save and Close

19. DEFINING MULTIPLE SELECTION CRITERIA FOR QUERIES • Multiple selection requires you to use logical operators • They combine two or more conditions • When you want to select a record, only if two or more conditions are met, use the ANDOperator • When you place conditions in different criteria rows, you use the OR logical operator • See Figure 3-25

20. The End !