Part II Query Types On the design query

Part II

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

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

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)

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

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

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

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

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.

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.

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.

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

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

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

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.

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

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

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

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 Location Enployee ID Employee ID Hire Date Last Name First Name. . . (Joined on common field) Location Job Title

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

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

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

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

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

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.

Select a calculation option

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 Ship. Country field and sorted on an Order. Date 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.

Questions