SECTION 3 SKILLS Creating Queries, Forms, and Reports

2. SECTION 3 SKILLSCreating Queries, Forms, and Reports 3.1 Use the Simple Query Wizard 3.2 Create a Query Using a Single Table 3.3 Add Multiple Tables to a Query 3.4 Add Criteria Statements to a Query 3.4 Hide Columns CHECKPOINT 1 3.5 Extract Records Using AND Criteria 3.5 Sort Query Results 3.6 Extract Records Using OR Criteria 3.7 Perform Calculations in a Query 3.8 Create and Edit Forms CHECKPOINT 2 3.9 Add Fields to a Form 3.10 Add a Logo 3.10 Resize and Edit Objects 3.11 Create and Edit a Report 3.12 Resize and Move Columns in aReport 3.12 Report Sections CHECKPOINT 3

3. Use the Simple Query Wizard • A query is an Access object designed to extract data from one or more tables. • Usually a query is created to select records that answer a question. • Query results display in a datasheet that pulls the data from existing tables. • A query can be created to serve a variety of purposes, from very simple field selection to complex conditional statements or calculations. • In its simplest form, a query can be used to display or print selected fields from two tables.

4. Use the Simple Query Wizard…continued To create a query using the Simple Query Wizard: • Click the Create tab. • Click Simple Query Wizard. • Click OK. • Choose the table(s) and field(s) to include in the query. • Click Next. steps continued on next slide… New Query dialog box selected fields

5. Use the Simple Query Wizard…continued • Choose Detail or Summary query. • Click Next. • Type a title for the query. • Click Finish. query title

6. Create a Query Using a Single Table To create a query in Design view with a single table: • Click the Create tab. • Click the Query Design button in the Queries group. • Double-click the required table in the Show Table dialog box. • Close the Show Table dialog box. • Add the required field names from the field list box to the columns in the design grid. steps continued on next slide… design grid

7. Create a Query Using a Single Table…continued • Click the Save button on the Quick Access toolbar. • Type a query name in the Save As dialog box. • Click OK. • Click the Run button in the Results group in the Query Tools Design tab. Save As dialog box Run button

8. Add Multiple Tables to a Query To create a query in Design view with multiple tables: • Click the Create tab. • Click the Query Design button in the Queries group. • Double-click the required tables in the Show Table dialog box. • Close the Show Table dialog box. • Add the required field names from the field list box to the columns in the design grid. steps continued on next slide… multiple tables

9. Add Multiple Tables to a Query…continued • Click the Save button on the Quick Access toolbar. • Type a query name in the Save As dialog box. • Click OK. • Click the Run button in the Results group in the Query Tools Design tab. Save As dialog box Run button

10. Add Criteria Statements to a Query To add a criteria statement to a query: • Open the query in Design view. • Click in the Criteria row in the column to attach the criterion to. • Type the criterion statement. • Save the revised query. • Click the Run button in the Results group in the Query Tools Design tab. criterion statement

11. Add Criteria Statements to a Query…continued

12. Hide Columns To hide columns: • Click the check box in the Show row in the desired column to clear the box. check box

13. CHECKPOINT 1 • This is an Access object designed to extract data from one or more tables. • datasheet • report • form • query • This command instructs Access to carry out the instructions and display the results of a query. • Run • Go • Start • Begin Answer Answer Next Question Next Question • This is the dialog box used to add tables to a query. • Query Table • Edit Table • Show Table • Add Table • Adding this type of statement to the query design grid will cause Access to display only certain records. • comment • criterion • description • objective Answer Answer Next Question Next Slide

14. Extract Records Using AND Criteria To extract records using AND criteria: • Start a new query in Design view. • Add the desired table(s) and field(s) to the design grid. • Click in the Criteria row in the first column. • Type the criterion statement. • Repeat Steps 3-4 for the remaining criterion fields. • Save the query. • Run the query. multiple criterion statements

15. Sort Query Results To sort query results: • Open the query in Design view. • Click in the Sort row in the field by which to sort. • Click the down-pointing arrow. • Click Ascending or Descending. • Save the query. • Run the query. Sort row

16. Extract Records Using OR Criteria To extract records using OR criteria: • Start a new query in Design view. • Add the desired table(s) and field(s) to the design grid. • Click in the Criteria row in the first column. • Type the criterion statement. • Click in the or row in the column in which to attach the next criterion. • Type the criterion statement. • Repeat Steps 5-6 as necessary, moving down one Criteria row for each new criterion. • Save the query. • Run the query. or row

17. Perform Calculations in a Query To create a calculated field in a query: • Open the query in Design view. • Click in the first available blank field row in the design grid. • Type a column heading for the calculated field. • Type a colon (:). • Type the mathematical expression. • Press Enter. • Click the Save button. • Click the Run button. mathematical expression

18. Perform Calculations in a Query…continued To format a calculated field: • Open the query in Design view. • Click in the field containing the calculated expression. • Click the Query Tools Design tab. • Click the Property Sheet button. • Click in the Format property box. • Click the down-pointing arrow. • Click the desired format at the drop-down list. • Close the Property Sheet task pane. • Click the Save button. • Click the Run button. Format property box

19. Create and Edit Forms To create a form using the Form button: • Click once to select the table name in the Navigation pane. • Click the Create tab. • Click the Form button in the Forms group. Form button

20. Create and Edit Forms…continued To change the theme: • Click the View button in the Views group of the Home tab to return to Layout view. • Click the Themes button in the Themes group of the Form Layout Tools Design tab. • Click the desired option at the drop-down gallery. Themes button

21. Create and Edit Forms…continued To create a form using the Form Wizard: • Click the Create tab. • Click the Form Wizard button. • Choose the table for which to create the form. • Select the fields to include in the form. • Click Next. • Choose the form layout. • Click Next. • Type the form title. • Click Finish. Form Wizard dialog box

22. CHECKPOINT 2 • Multiple criteria entered in the same Criteria row becomes this type of statement. • AND • OR • ELSE • SUM • This is the character between the text and the mathematical expression for the values. • plus sign • minus • colon • asterisk Answer Answer Next Question Next Question • Multiple criteria entered in different Criteria rows becomes this type of statement. • AND • OR • ELSE • SUM • Use this to be guided through a series of dialog boxes to generate a form. • Form button • Form Wizard • Form Generator • Form box Answer Answer Next Question Next Slide

23. Add Fields to a Form To add fields from another table to the form: • Open the Form. • Switch to Layout view. • Click the Add Existing Fields button in the Tools group of the Form Layout Tools Design tab. • Click Show all tables. steps continued on next slide… Add Existing Fields button Show all tables option

24. Add Fields to a Form…continued • Click the expand button next to the desired table. • Drag the desired field from the Field List pane to the desired location on the form. • Repeat Step 6 for all fields to be added. • Close the Field list pane. • Save and close the form. expand button Field List pane

25. Add a Logo To add a logo to a form: • Open the form in Layout view. • Click the logo container object. • Click the Logo button in the Header/Footer group in the Form Layout Tools Design tab. • Navigate to the location of the graphic file. • Double-click the graphic file name. Logo button logo container object

26. Resize and Edit Objects To resize a control object: • Open the form in Layout view. • Select the desired control object. • Point to the left, right, top, or bottom edge, or to a corner. • Drag the height or width to the desired size. pointer

27. Resize and Edit Objects…continued To edit a control object: • Click to select the label control object. • Click the label control object a second time to place an insertion point inside the selected control. • Position the insertion point. • Add or remove spaces or characters as desired. insertion point

28. Create and Edit a Report To create a report: • Click the object name in the Navigation pane. • Click the Create tab. • Click the Report button in the Reports group. Report button

29. Create and Edit a Report…continued To change to landscape: • Click the Report Layout Tools Page Setup tab. • Click the Landscape button in the Page Layout group. Landscape button

30. Resize and Move Columns in a Report To resize report columns: • Open the report in Layout view. • Click the column heading. • Drag to the right or left border of the selected column heading to the desired width. • Save the report. resize pointer

31. Resize and Move Columns in a Report…continued To move report columns: • Open the report in Layout view. • Click the column heading. • Shift + click over the data below the column heading. • Position the mouse pointer inside the selected column. • Drag the column to the desired location. • Save the report. move pointer

32. Report Sections

33. CHECKPOINT 3 • The Add Existing Fields button is in this group of the Form Layout Tools Design tab. • Views • Tools • Themes • Controls • Use this object when you want to specify which fields to print. • table • form • query • report Answer Answer Next Question Next Question • Use this key to select multiple control objects in a form or report. • F1 • Alt • Shift • Ctrl • This is the default orientation. • Portrait • Landscape • Full • AutoFit Answer Answer Next Question Next Slide