Access Queries

1. Access Queries

2. Queries • Most common type of Query is selection(projection) • Specify sources for data retrieval • table(s) and/or query(ies) • Specify attributes(fields) to be retrieved • Selection criteria • Calculations that need to be performed • Action Queries • Other types such as crosstabs

3. Query Languages • Relational Algebra • Provides the conceptual basis for SQL and QBE • Structured Query Language (SQL) • The user enters commands according to a pre-defined syntax to retrieve desired data. • Query By Example (QBE) • The user starts with a sample of the table(s) columns and marks the fields he or she wants to include in the answer. • Defaults are available for summarizing and manipulating the data.

4. Creating Queries • Query Wizard • QBE Query Design • SQL design (beyond scope of our class)

5. Creating Query 2k7 • Wizards • Click on create tab • Select query wizard • Select simple query • Select your data source(s) • Select desired attributes from each data source • Select next • Name query • Click on finish

6. Wizard Click on create tab and select Query Wizard

7. Click on Simple Query wizard

8. Select data sources and desired attributes from each source

9. Name your query

10. Resulting Dynaset

11. Access creates the SQL needed to do the query for the wizard • SQL for previous query example

12. Design View • Uses QBE (what we will use) • Design View • Click on create tab • Select query design • Select your data sources and click on add • Select attributes from each data source and drag to grid below • Run query • Save query if it will be used on a regular basis

13. Click on create tab and select Query Design

14. Select your data sources and click on add

15. Select your data sources and click on add

16. Select attributes from each data source and drag to grid below

17. Click on Run symbol to execute query

18. Resulting Dynaset

19. Access creates the SQL needed to do the query • SQL for previous query example

20. Examples to follow based on this database

21. Queries with selection criteria • Use design view when using selection criteria • Select your data sources and attributes if interest from your data sources enter selection criteria • Run query

22. Query to list Customers from Brazil Selection Criteria

23. Resulting Dynaset

24. Comparison operators <, >, =, <=, >=, and <> Not equal to Logical operators And, Or, and Not Queries(complex selection criteria)

25. Multiple Criteria • AND operator • Queries that must satisfy more than one condition, with all conditions being true, use the ANDoperator • OR operator • When any one of several criteria is all that is required for a row to be displayed in a dynaset, then you use the OR operator

26. And Operator Selection Criteria

27. Resulting Dynaset

28. OR Operator Selection Criteria

29. Resulting Dynaset

30. Action queries • Action Queries • create tables, • Delete/remove records, • update fields, or • Append/add new records to an existing table • Action queries alter information as opposed to selection queries, which passively display information without altering anything • No undo feature

31. Make Table • Use design view • Select source table • Select attributes • Select make table query symbol and enter name for your table in pop-up box • Run query • You will receive a message about the number of records being added to table • Save query if it will be used in the future

32. Select source table Select attributes Note use of wildcard *. Wildcard selects all attributes

33. Select make table query symbol and enter name for your table in pop-up box

36. Make Table Results

37. Delete Query • Use design view • Select source table • Select records/attributes selection criteria • Select delete query symbol • Run query • You will receive a message about the number of records being deleted • Save query if it will be used in the future

38. Note there are currently 91 records in the Norhtwind Traders customer table

39. Select delete query symbol Criteria for deletion

41. DELETE tblCustomersUSA.*, tblCustomersUSA.Country FROM tblCustomersUSA WHERE (((tblCustomersUSA.Country)="usa")); Note there are now 78 records in the Norhtwind Traders customer table

42. DELETE tblCustomersUSA.*, tblCustomersUSA.Country FROM tblCustomersUSA WHERE (((tblCustomersUSA.Country)="usa"));

43. Update Query • Use design view • Select source table • Select attribute(s) to be updated • Select update query symbol • Fill in update to criteria • Run query • You will receive a message about the number of records being updated • Save query if it will be used in the future

44. Select attribute(s) to be updated Select update query symbol Update to Criteria

46. Append Query • Use design view • Select source table(s) • Select append query symbol • Identify destination table that source table will be appended to • Drag fields from source to grid • Access will automatically map fields from source and destination that have same name • Manual map for all others • Run query • You will receive a message about the number of records being updated

47. Select table to append

48. Select append Query Symbol Select Append to Table and click on ok

49. Select field and drag to grid below • Notice that field mapping has been done for most fields automatically • Must map to like type data

50. Manual mapping for this field(ZipCode to PostalCode)