Queries

1) Note to class: When completing queries in Access, create and save a new query for each query

i) Do not use “Edit, Clear Grid”

2) Types of Queries:

i) Select Queries

(1) Ask a question of the database and get an answer

ii) Action Queries

(1) Change multiple records in the database with a single operation

3) Select Queries

i) Ask a question of the database and get an answer

(1) Design grid (Question)

(2) Specify search criteria

(3) Query searches underlying table and displays resulting records in a dynaset

(4) Dynaset (Answer)

1. Lists records from query

i. Records can be added, deleted, or modified

ii. Any change is reflected in underlying table

4) Create Select Query in design view:

i) With the database window open, point to the object list on the left and click on “Queries”

ii) Double click on “Create query in Design view”

iii) Show table dialog box allows you to select the tables or queries that you wish to use in selecting fields for the query

iv) Query Design window shows:

(1) Field list (top of window)

1. Drag and drop fields from list to Design Grid as necessary

(2) Design grid (bottom of window)

1. After fields are added, create question with field rows:

i. Field row

1. Specify field

2. Highlight column headings and drag and drop to rearrange field order as necessary

ii. Sort row

1. Ascending or Descending

1. Major Key must appear to the left most part when compared to minor keys

iii. Show row

1. Determines whether field will be shown in dynaset

iv. Criteria row

1. Determines records to be selected

v. Design grid “Criteria” details:

1. Data type for field determines how criteria is specified

1. Text

i. Criterion is in quotation marks

ii. Access automatically adds “”

2. Number, currency, counter

i. criteria shown as digits

ii. Decimals o.k.

iii. Dollar signs not o.k.

iv. Commas not o.k.

3. Date fields

i. Criterion form: #mm/dd/yy#

ii. Access automatically adds ##

4. Yes/No fields

i. Yes = True

ii. No = false

5. Wild Card

i. Allows you to search for a pattern within a text field

* = any number of characters in the same position as *
? = single character in same position as ?

2. Selection Criteria

1. Type in search criterion:

i. Business = business = BUSINESS = “Business” = “=Buisness”

2. And Condition

i. Criteria entered in same row

ii. Records must meet all of the specified criteria

3. Or Condition

i. Criteria entered in different rows

ii. Records may satisfy any of the specified criteria

iii. Any one of the criteria

4. Relational operators

5. Functions

i. Between Function (type: “Between 1000 and 2000”)…

ii. Not Function (type: “Not “Puzzles””)

(3) Use F6 key to toggle between Field list and Design grid

(4) Click Datasheet View at anytime to view dynaset (answer)

1. As you create question, you can toggle back and forth between datasheet view and design view

v) You can ask a question of more than one table at a time

(1) Show two tables and draw fields from both tables

(2) Joining Tables within a query

1. Two tables that have the same matching fields

2. When you show two tables that have the same field names, the “join line” automatically shows

i. (Drag manually if desire)

3. Good picture on page A2.29

(3) Calculated Fields

1. Must show “Total” row

2. Right-click the grid, Totals

3. Type Calculated Field Name and Colon

4. All Field Names must be in square brackets [ ]

5. Use ordinary Math symbols (same as Excel)

6. Use functions

i. Click Arrow and display drop down list of functions

7. Right-click Zoom shows large view

8. Right-click to show properties and formats such as currency or percentage

9. Aggregate Functions:

COUNT

SUM

AVG

MAX

MIN

STDEV

VAR

FIRST

LAST

10. Where

i. “Where” in Totals column lets you specify which records to perform the calculation upon (type criteria into criteria row)

ii. A check can not be placed in the show box or the query will not run

11. Group By

i. Group records within query

5) Other details for queries:

i) Include all fields in query, use:

(1) *

(2) Highlight all fields, hold shift key, drag fields to design grid

ii) Clear grid

(1) Edit, Clear Grid

6) Action Queries (Access Chapter 3)

i) Change multiple records in the database with a single operation

ii) Four types of Action Queries:

(1) Update Query

1. Change multiple records within the table

2. Use criteria in query to update records

(2) Make-Table Query

1. Makes a new table from records in an existing table

2. Useful to run before running a delete query (backup)

(3) Delete Query

1. Deletes records

2. Use criteria in query to delete records

(4) Append Query

1. Adds records from one table to the end of another table

2. Use criteria in query to append records

3. Lower Right Page 3.27