1 / 29

Access

Access. Part II. More on Queries. query types relationships joins mulitable queries creating a calculated field creating summary queries. Query Types. On the design query table pane, right click and the cascading window will appear. There are 5 general query types. Select crosstab

summerwhite
Télécharger la présentation

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 Part II

  2. More on Queries query types relationships joins mulitable queries creating a calculated field creating summary queries

  3. Query Types On the design query table pane, right click and the cascading window will appear.

  4. There are 5 general query types • Select • crosstab • parameter • action • SQL

  5. Select • Select query - retrieves the specific data you request from one or more tables, then displays the data in query datasheet in the order you specify. • This is the most common type of query. (also the one we have concentrated on)

  6. Crosstab • Crosstab query - Summarizes large amounts of data in an easy-to-read, row-and-column format

  7. Parameter • Parameter query - Displays a dialog box prompting you for information, such as criteria for locating data. • For example, a parameter query might request beginning and ending dates, then display all records matching dates between the two specified values

  8. Action query • Makes changes to many records in one operation. • There are 4 types: • make-table - creates a new table from selected data in one or more tables • update - makes update changes to record, such as when you need to raise the cost of material by 5% • append - add records from one or more tables to end of other tables • delete - deletes records from a table or tables

  9. SQL • Created using SQL (Structure Query Language) • an advanced programming language used in Access

  10. Working with more than one table • Large tables are difficult to manage • most likely will have redundancy • A join is an association that tells Access how data between tables is related • A relationship is established between tables usually through at least one common field.

  11. Related tables • Tables that are related must share at least one common field • The data type for the common field must be the same in tables being related • you cannot relate a text field to a date/time field • Often the fields have the same name, but this is not a necessary requirement.

  12. Joining Tables • To join tables, you first bring both tables to the upper pane of the design query window. • Access will draw a line, called a join line, between matching fields from either table. • Matching fields are fields with the same name where one of fields is a primary field. • This is called a default join or an inner join.

  13. Joining Tables • If you fail to give the matching fields the same name, Access will not automatically insert the line and create the join. • You can create the join manually by dragging form one common field to to the other. • The join instructs the the query to check for matching values in joined fields. • When matches are found, the matching data is added to the query datasheet as a single record

  14. Three types of relationships • one-to-many • many-to-many • one-to-one

  15. One-to-many • A record in table A can have many matching records in table B, but a record in table B have only one matching record in table A • Example: one instructor may teach one or more sections

  16. Many-to-many • A record in table A can have many matching records in table B, and a record in table B can have many matching records in table A • Example: Instructors can teach many courses.

  17. Example of a many-to-many using a third linking table

  18. One-to-one • A record in table A has only one matching record in table B, and a record in table B has only one matching record in table A. • Example: an employee information table and an employee compensation table

  19. Defining • using Tools->Relationships, you can define permanent relationships between tables that will enforce the rules of referential integrity

  20. Two tables in a one-to-one relationship. When the Employee ID fields of the two tables are joined, a query can be created using data from both tables Employees Enployee ID Hire Date Last Name First Name ... Location Employee ID Location Job Title (Joined on common field)

  21. Customers * Customer Id First Name Last Name Age Credit Card * Customer Id Credit limit ... Design Query Display in alphabetical order the last names of customers that have a credit card with “us” Field: First Name Last Name Customer Id Table: Customers Credit Card Customers Sort: Ascending Show: Criteria Or:

  22. The Results • A list will appear when the run command is clicked, the ! Icon. First Name Adam Eve Susan John Joseph Harold Last Name Abrahams Cane Duly Done Jackson Smith Customer Id 14566 34564 56734 59432 57778 89576 Both tables must contain matching records in order for a record to appear in the query’s result *

  23. Calculated Field • A calculated field enables you to draw the data form other field columns and perform a mathematical calculation on a row-by-row based • Generally, if a value can be calculated for display, it should not be stored permanently in the database

  24. Create • Select an empty field column in the query design grid, click the expression builder to help you to enter expression. (the magic wand) • example syntax: • Nameof field in datasheet window:expression • example NEW GRADE: expression

  25. Creating • Select an empty field column in the query design grid, click the expression builder to help you to enter expression. (the magic wand) • syntax: • Name of field in datasheet window:expression • Expression - can use a field name, operators and numbers • example: • multiply the field purchase ( in table Customers) by 5% and call this field Tax • Tax:[Customers]![purchase] * .05

  26. Creating Summary Queries • Enables to perform aggregate calculations to summarize a group of data. • Right click on the design grid and a cascading menu will appear, the top will be be Totals. Select totals, an extra row will appear in the design grid called Totals: • select a cell in the Total row of the desired column.

  27. Select a calculation option

  28. First and Last • The First and Last functions are most useful in calculated controls on a report. For example, if you have an Order report that is grouped on a ShipCountry field and sorted on an OrderDate field, you can use the First and Last functions in calculated controls to show the range of earliest to latest order dates for each grouping.

  29. Questions

More Related