Transcript Access Queries - University of Nevada, Reno


Agenda 10/1/12
o Review quiz, answer questions
o Highlight key issues about queries
• Answer any questions about queries to prepare you to complete part 2
of the Access Project (due 10/10).


Turning data stored in a database into information for
decision making.
Table vs query:
o What is the difference between a query and a table?
o What is the difference between a query datasheet and a table
datasheet?
o Why do we create queries when we already have the data in tables?

Table contains structure of data, constraints and actual
data.
o Table is referred to as “underlying data”.

Query is a way to look at the data.
o Queries seldom look at the complete contents of a table because
tables are usually very big, with many columns and many rows.
o A query reduces the number of rows and columns in the underlying
data (tables) to provide information for decision making.
o Queries “filter” the data; fewer columns, fewer rows, calculated
fields, summarized information.
o A query enhances the data in the underlying tables by added
calculations and logical conditions.





Design view: Used to structure a query. Referred to as
“query by example” or QBE.
Result table: The table produced by the query. Shown in
the datasheet view.
SELECT query window: The window displayed in design
view that is filled out to produce a result table. Also called
the query design grid.
Field row: The area in the SELECT query window used to
define what columns should appear in the result table.
Criteria row: The area in the SELECT query window used to
identify which rows should appear in the result table.

Generalized queries
o Using one or multiple tables
o Specify the fields and records you want to Select.

Aggregate queries
o Create one line in the result table.
o Create multiple groups in the result table.

Parameter queries
o Type of query that prompts the user for input before it runs. The
query then uses the input as criteria that control your results.
Example: City Parameter query in Panorama database.



Referred to as “joining” tables.
Can produce confusing results.
Very dependent on a well-designed database. The tables
must be related with appropriate foreign keys or the tables
cannot be joined correctly for queries.


Used in criteria to specify what records to include in the
query.
Normally, must be very explicit about relational operators
on the conditions of queries.
o =, >, <, >=, <=
o Like
o Between
o In
o Is

Wildcard is an asterisk.



Can do calculations for a column based on the data in other
columns for that same row.
Can use mathematical operators.
Can use pre-written functions in MS Access. Many different
types of pre-written functions for date handling, data type
conversion, calculations, etc.
o See the pre-written functions in the expression builder.

Can be very simple to very complicated.


Aggregate functions are used to create grouped output.
Common summary calculations have pre-written functions:
o Sum, count
o Max, min
o Avg, stDev, var
o First, last

Calculations can be done for all data in the result table or to
create grouped output in the result table.

Criteria
o Relational operators: <, >, =, In, Between, Like
o And vs. Or
o Wildcards

Multiple tables
o Tables must have a common field


Calculations
Logical operators
o And, Or, IIF, Not

The IIf (Immediate If) function assigns one value to a
calculated field or control if a condition is true, and a
second value if the condition is false.



Frequently want to see if something is TRUE or FALSE.
Example: If a training event has > $500 in expenses, then
it is a high expense event.
Logical condition for Access:
o IIF(trainingexpenses + travelexpenses > 500, “high expense”)
o IIF(ISNULL(trainingexpenses + travelexpenses), 0)
o IIF(ISNULL(trainingexpenses + travelexpenses), 0, trainingexpenses
+ travelexpenses)