Transcript Access Queries - College of Business


Agenda 6/17/13
o Review quiz, answer questions
o Create relationships through “Lookup tables”
o Discuss queries:
• Turning data stored in a database into information for decision making.
• Highlight key issues about queries
• Answer any questions about queries to prepare you to complete part 2 of
the Access Project.


A lookup field lets the user select a value from a list of
possible values
You use a Lookup Wizard field in Access to create a lookup
field in a table
o Example: Contract – ContractType relationship
2



Turning data stored in a database into information for
decision making.
A query creates a “virtual table”.
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.





Each value in a field has very specific data coded for a
computer to read.
Humans can discern vague similarities and differences
among data fairly easily. Computers are more exacting.
Computers need you to tell them when data is a date, or a
character, or a number.
A zero is not the same as a blank which is not the same as
a null.
A null is a special character assigned to a field that
technically has “no value”. It is very useful because we can
search for a null value with special operators.

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.
• OR
o Create multiple groups in the result table.
o Use the “Totals” Button

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.
All tables in the query must be joined for the query to work
properly!



Computers require very explicit instructions.
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 – Define a condition with a list of two or more values for a field
o Is

Wildcard is an asterisk.


You use the logical operators to combine two Boolean
values and return a true, false, or null result. Logical
operators are also referred to as Boolean operators.
And: Returns True when Expr1 and Expr2 are true.
o Example: InvoiceAmt >$500 And City = Lansing

Or: Returns True when either Expr1 or Expr2 are true.
o Example: InvoiceAmt > $500 OR City = Lansing

Not: Returns True when Expr is not true.
o Example: Not In ("holland","rockford","saugatuck")



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.




To perform a calculation, you define an expression containing a
combination of database fields, constants, and operators
o Expression Builder is an Access tool that makes it easy for
you to create an expression
In the Query Setup group on the Design tab, click the Builder
button
Use the expression elements and common operators to build
the expression, or type the expression directly in the
expression box
Click the OK button
13
 You
can specify a particular format for a calculated
field, just as you can for any field, by modifying its
properties
14
 The
& (ampersand) operator is a concatenation
operator that joins text expressions, the plus sign
(+) has the same function.
 The IsNull function tests a field value or an
expression for a null value; if the field value or
expression is null, the result is true; otherwise, the
result is false
15


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.
The Group By operator divides the selected records into
groups based on the values in the specified field

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(ProductCost > ProductPrice, “Losing Money”)
o IIF(ISNULL(trainingexpenses + travelexpenses), 0)
o IIF(ISNULL(trainingexpenses + travelexpenses), 0, trainingexpenses
+ travelexpenses)