Microsoft Access 2010

1. MicrosoftAccess 2010 Chapter 2 Querying a Database

2. Objectives • Create queries using Design view • Include fields in the design grid • Use text and numeric data in criteria • Save a query and use the saved query • Create and use parameter queries • Use compound criteria in queries • Sort data in queries Querying a Database

3. Objectives • Join tables in queries • Create a report and a form from a query • Export data from a query to another application • Perform calculations and calculate statistics in queries • Create crosstab queries • Customize the Navigation Pane Querying a Database

4. Create folder for this chapter • Create a Ch. 2 folder under your Access folder in your home directory • Copy the Camashaly Design database from your chapter 1 folder to your chapter 2 folder to use it for our new projects. Querying a Database

5. Project – Querying a Database Read Project – Querying a database on pg. 74 Querying a Database

6. General Project Guidelines – for Queries • Identify the fields – which fields from which tables? • Identify restrictions – does the query include ALL records or a restricted group? • Determine whether special order is required – should you sort the data? • Determine whether more than one table is required – special items to take care of if items are from more than one table • Determine whether calculations are required – Group totals, individual totals, etc. • If data is to be summarized, determine whether a crosstab query would be appropriate – if data is to be grouped by 2 different types of information, a crosstab query will work great! Querying a Database

7. Creating a Query in Design View • Open the Camashaly Design database in your chapter 2 folder. • Previously we used the query wizard, now we will create queries in Design view. • Click on the Create Ribbon, then on the Query Design button. • Add the Client Table (double click, or click then click add button) • Close the Show Table dialog box. • Resize the box so you can see all the fields Querying a Database

8. Creating a Query in Design View Querying a Database

9. Adding Fields to the Design Grid • Do page 80-82 You can use the View button or the Run button to see the results of a query. Querying a Database

10. Using Saved Queries • Easily change the layout to modify it for future queries • Can be easily printed Querying a Database

11. Using a Wildcard • Wildcard – a symbol that represents any character or combination of characters • * - represents any collection of characters • ? – represents any individual character • Do page 83-86.. Call me over and let me see it. Querying a Database

12. Using a Wildcard Querying a Database

13. Parameter Query • Sometimes we need to allow the user to enter information which needs to be searched for. Querying a Database

14. Creating and Viewing a Parameter Query Build a parameter query.. Pg. 87-91 Querying a Database

15. Using a Compound Criterion Involving AND • AND – both criterion must be true (same row) • OR – Either criterion must be true (different rows) Querying a Database

16. Using a Compound Criterion Involving OR • Do pages 91-93 Querying a Database

17. Special Criteria – pg. 94 • Range of values for a single field – see example 1 • BETWEEN operator – example BETWEEN 1000 and 4000 • IN operator – IN followed by a list of values in parenthesis. For example IN (“11”, “14”) Querying a Database

18. Sort Order • What is the sort key? • Is there more than one sort key? (Major and Minor) Major sort field MUST appear to the left of the minor sort field in a query! • Are there any restrictions? One common restriction is to exclude duplicates. Another is to list only a certain # of records Querying a Database

19. Clearing the Design Grid • Open the query in Design view • Click just above the column heading in the first column in the grid to select the column • Hold the SHIFT key down and click just above the last column heading to select all the columns • Press the DELETE key to clear the design grid • Now do pages 96-99 Querying a Database

20. Creating a Top-Values Query • This allows you to show only a specified number of records (top 5, top 10, etc.) • Do pages 96-100 Querying a Database

21. Joining Tables – do you need more than one tables’ information? Finding records in two table that have matching values. See page 101 Querying a Database

22. Joining Tables • Click the Query Design button (Create tab | Queries group) to create a new query • Add two related tables to the new query • Add the desired fields from each table to the query • Next slide Querying a Database

23. Joining Tables For the JOIN to happen properly… the fields have to be spelled EXACTLY alike, be the same data type, width, etc. If you still don’t have a join line.. .create one yourself… I will show you how. Querying a Database

24. Joining Tables Do pages 102-108 DO NOT PRINT THE REPORT! Querying a Database

25. Creating a Form for a Query • DO pages 109-110 Querying a Database

26. Exporting Data to Excel • There are many times that others may need the data you have in your tables in some other format…either Word or Excel • We have already imported from Excel, now let’s export to excel. • There are steps here also to create a .rtf file or a regular text file. • Do pages 111-113 Querying a Database

27. Restricting the Records in a Join • Do page 115 Querying a Database

28. Using a Calculated Field in a Query • Is there something that you need to calculate on the fly? If so, • Choose a name for the calculated field • Determine the format for the calculated field (currency, # decimal places, etc.) Querying a Database

29. Using a Calculated Field in a Query Do pages 116-119 Querying a Database

30. Using Criteria in Calculating Statistics • There are some built-in statistics such as COUNT, SUM, MIN, MAX, STDEV, VAR, etc. • These are called aggregate functions – they perform mathematical functions against a group of records. • Do pages 119-122 Querying a Database

31. Using Grouping Grouping – creating groups of records that share some common characteristic. Do page 122-123 Querying a Database

32. Crosstab Queries Crosstab queries calculate a statistic (sum, avg, count) for data that is grouped by 2 different types of information. One group will be horizontal and the other vertical (rows and columns) Do pages. 124-128 Querying a Database

33. What now? • Homework • Learn it Online – • Pg. 129- Who wants to be a Computer Genius? • Make sure you choose Chapter 2 • www.scsite.com/ac2010 • You can do a screen capture or print it.. But you get what you get, got it? • Labs • In the Lab, Lab 3 – Philamar Training DB – pg. 135 • Cases & places Case 1 – Chamber of Commerce – pg. 135-136 • BEFORE you can do the labs for this chapter… you better make REALLY sure that your tables are corrected from ch. 1 • Test - TBD Querying a Database