1 / 14

Access

Learn the importance of table relationships in combining related data and how to use queries to obtain specific data. Topics include filters, sorting, record selection criteria, expressions, operators, calculated fields, aggregate functions, and grouping.

croom
Download Presentation

Access

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. Access Chapter 3-Obtaining Answers to Your Data Questions

  2. Table Relationships • Defining and using table relationships is the way to combine related data • One to many • Unique record from one table can have zero, one or many related records in another table PgP MIS 342 Access

  3. Table Relationships • Referential Integrity • System of rules to ensure that relationships between records in related tables are valid • Prevents accidental deletion of or change to related data • Use cascade updates and or cascade deletes to allow automatic changes to related fields • (is it good to use cascading? Depends on the situation) PgP MIS 342 Access

  4. Querying Databases • Filters-datasheet tools to temporarily display set of records • Filter by Selection • Filter by Form • Advanced Filter PgP MIS 342 Access

  5. Querying Databases • Most common type- Select Query • Asks a question about the data stored in tables, only certain records meet the criteria and are selected PgP MIS 342 Access

  6. Querying Databases • Query, Design View • Query By Example (QBE) is the Access user interface that creates SQL statements • Query, Datasheet View • The display of the returned records • Can be different each time the query is run PgP MIS 342 Access

  7. Querying Databases • Creating queries is one of the most important skills in any database program • Queries are the basis for forms and reports • Good form and report design is a result of starting with a good query design PgP MIS 342 Access

  8. Querying Databases • Sorting, Ascending or Descending • Up to 10 fields, done from left to right • Show check box- determines if field is displayed in datasheet • Why needed? Set criteria for a field that you do not want displayed PgP MIS 342 Access

  9. Querying Databases • Specifying Record Selection Criteria-learning to return just the records you need PgP MIS 342 Access

  10. Querying Databases • Learn to formulate expressions, used in: • Query criteria and calculated fields • And also: • Table validation rules • Form and report calculated controls • VBA code • SQL statements PgP MIS 342 Access

  11. Querying Databases • Operators- key expression building tool • Like, In, Between…And… • =, <, >, <> • Exact matches • Multiple criteria • And-tends to return fewer records • Or-tends to return more records PgP MIS 342 Access

  12. Querying Databases • Calculated Fields-derivable quantities that should not be stored (why?) in underlying table, such as: • ExtendedPrice: Quantity * Price • Age: DateDiff("yyyy",[DateOfBirth],Date()) • Expression Builder-useful tool • Zoom Box (Shift+F2) PgP MIS 342 Access

  13. Querying Databases • Aggregate Functions-useful for gathering statistical information, watch “group by” PgP MIS 342 Access

  14. Querying Databases • Group By-use in conjunction with aggregate functions or alone • Consolidate records into groups based on chosen fields PgP MIS 342 Access

More Related