1 / 40

Lab 3

Lab 3. Analyzing Tables and Creating Reports. Objectives. Filter table records. Create a query. Move columns. Query two tables. Create a report. Modify a report design. Print a selected page. Concepts.

rogan-rich
Download Presentation

Lab 3

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Lab 3 Analyzing Tables and Creating Reports

  2. Objectives • Filter table records. • Create a query. • Move columns. • Query two tables. • Create a report. • Modify a report design. • Print a selected page.

  3. Concepts • Filter - A filter is a restriction placed on records in the open datasheet or form to temporarily isolate and display a subset of records. • Query - A query is a request for specific data contained in a database. Queries are used to view data in different ways, to analyze data, and even to change existing data. • Join - A join is an association between a field in one table or query and a field of the same data type in another table or query.

  4. Concepts • Report - A report is printed output generated from tables or queries. • Controls - Controls are objects on a form or report that display information, perform actions, or enhance the design.

  5. Outline • Filtering Records • Using Filter by Selection • Using Filter by Form • Querying a Database • Creating a Query Using a Query Wizard • Moving Columns • Modifying a Query • Creating a Query in Design View • Querying Two Tables

  6. Outline • Creating Reports • Using the AutoReport Wizard • Zooming the Report • Using the Report Wizard • Modifying the Report Design • Selecting Controls • Moving Controls • Sizing Controls

  7. Outline • Printing a Report • Changing Page Margins • Printing a Selected Page • Creating a Report from a Query • Compacting the Database • Lab Review

  8. Concept 1: FILTER • Restriction placed on records • Quickly isolates and displays a subset of records • You specify a criteria • Limiting conditions • Temporary Filter • Filter results can't be saved • Filter criteria is saved with the table

  9. USING FILTERS • Filter by Selection • Displays only records containing a specific value • Effective when only one value is needed • Other records are temporarily hidden • Filter by Form • Perform filters on multiple criteria • Look For tab • Or tab

  10. USING FILTERS • Criteria expression • Create one or… • Select value from drop down list • AND operator • OR operator

  11. QUERYING A DATABASE • A query is a request for information • View data in different ways • Analyze data • Can change existing data • Used as a source for forms and reports

  12. Concept 2: QUERY

  13. CREATING A QUERY The Query Wizard

  14. USING A QUERY WIZARD Steps • Select underlying table and fields • Specify a name for your query • Select to open as is or in Design view

  15. MODIFYING A QUERY • Query Design window • Field list • Design grid • Column selector • Row label • Cell • Show boxes

  16. CREATING A QUERY IN DESIGN VIEW

  17. Concept 3: JOIN • An association between a field in one table and a field in another table • Field must be same data type • Common fields • Queries can be joined • Used to bring information from different tables together • Joins are the power of relational databases

  18. JOINS

  19. Concept 4: REPORT • Printed output generated from tables or queries • All fields or selected fields • Can be based on a table or query • Can customize: • Text formats • Styles • Layouts • Grouping • Summary information

  20. CREATING REPORTS • AutoReport Wizard • Report Wizard What's the difference?

  21. MODIFYING THE REPORTS DESIGN

  22. Concept 5: CONTROL • Controls are objects on a form or report • Display information • Perform actions • Enhance the design • Two basic types of controls: • Bound • Unbound

  23. Labels Text boxes Check boxes List boxes Command buttons Lines Rectangles Option buttons ACCESS OBJECTS HAVING CONTROLS

  24. MORE ON CONTROLS • Selecting controls • Moving controls • Sizing controls

  25. PRINTING A REPORT • Changing page margins • Printing a selected page

  26. CREATING A REPORT FROM A QUERY

  27. COMPACTING THE DATABASE • Makes a copy of the file • Rearranges how the file is stored on disk • Makes database perform optimally • Compacting is important because: • Database becomes fragmented due to deletes • Space not used efficiently

  28. Key Terms • AND operator– Narrows a search because a record must meet both conditions • Bound control– A control that is linked to a field in an underlying table. • Cell – Space created by the intersection of a vertical column and a horizontal row. • Column selector bar– The thin gray bar just above the field name in the grid in Query Design view.

  29. Key Terms • Common field– A field that is found in two or more tables. • Compact– Making a copy of a database file for optimal performance. • Controls - In Form and Report Design views, information such as text boxes that tell Access where to place data and text. • Criteria– A set of limiting conditions. • Criteria expressions - An expression that will select only the records that meet certain limiting criteria.

  30. Key Terms • Design grid - The lower part of the Query Design window, which displays settings that are used to define the query. • Field list - A small window that lists all fields in an underlying table. • Filter - A restriction placed on records in an open form or datasheet to temporarily isolate a subset of records. • Filter by Form - This feature provides a blank version of the current form or datasheet.

  31. Key Terms • Filter by Selection - A type of filter that displays only records containing a specific value. • Join - Creates a relationship between tables by linking common fields in multiple tables. • Join line - In the Query Design window, the line that joins the common fields in multiple tables. • Margin - The blank space around the edge of a page. • Move handle - A large box used to move a control picture. • Multi-table query - A query that uses more than one table.

  32. Key Terms • OR operator - Any record meeting either condition is included in a search. • Query - Used to view data in different ways, to analyze data, and to change data. • Query datasheet– Where the result or answer to a query is displayed. • Report– Printed output generated from tables or queries. • Report Design view– Used to create and modify the structure of a report. • Row label– Specifies the type of information that can be entered in a query.

  33. Key Terms • Show box– Used to display a field in a query result. • Sizing handles– Small boxes used to size an object. • Unbound control– Text not connected to a field.

  34. Discussion Questions • Discuss what filters are and how they can be used in a database. When would it be appropriate to use a filter? • Discuss the differences between the AND and OR filter conditions. • Discuss what a query can do and some advantages of using queries. • Discuss the different types of controls. Give an example of how they can be used to create different report designs.

  35. Frequently Asked Questions • What's the difference between Filter by Selection and Filter by Form? • How do I remove a filter? • What is required to create a query? • I created a query, but nothing happens. Did I forget to do something?

  36. Frequently Asked Questions • When would I use a crosstab query? • Running a query does not save the results, but I want to run my query again. What should I do? • When would I use a join? • What is the difference between forms and reports?

  37. FAQ cont. • I am not sure how to create a report. What should I do? • I want to change my report. What do I need to know about the controls? • I printed a report and some of the pages are blank. What should I do?

  38. Web Links • Access – Linked Tables • http://msdn.microsoft.com/library/officedev/office97/ORKht/T.HTM • Access Utility Library – Shareware • http://www.emsps.com/access.htm • MS Access Tutorials – Parameter Queries • http://mis.bus.sfu.ca/tutorials/MSAccess/tutorials/param.pdf

  39. Web Links • MS ACCESS Tutorial – Creating Database Relationships in Access • http://databases.miningco.com/compute/databases/library/weekly/aa031801a.htm • MS Access Tutorial - Creating a Report in Access • http://databases.miningco.com/compute/databases/library/weekly/aa122400a.htm

  40. Web Links • Filters and Queries – Access • http://mis.commerce.ubc.ca/courses/comm391/tutorials/access1/access1.htm • MS Access – Queries • http://www1.harlingen.isd.tenet.edu/images/helppages/MSAccessTutorials.html • Gaining Access – What is a Query? • http://www.psu.edu/dept/cac/ets/projects/modules/ga/query_index.html

More Related