Getting started with queries

1
Getting started with queries

• Our HR/Vantage tutorial covers queries in great
  detail
• Like other topics, it is also covered in your
  book
• This tutorial will be mercifully brief...

2
Retrieving sorting data

• To make data useful, you need to get it back out
  of the database
• In MS Access, we use a technique called query by
  example, where you fill out a worksheet that
  shows what you want, and the programs gets it for
  you
• Queries are also called views
• An MS Access query creates a recordset that is
  just like a table, for all practical purposes

3
ActRep example

• I added a three records to the people table

4
ActRep example

• and I added a bunch of records to the
  activities table (by copy and paste append, so
  they look similar)

5
Queries are questions!

• What are all the books published by members of my
  department?
• What are all the activities that they want to
  share externally?
• What are all the activities by Professor Cookie
  so far this year?

6
Queries are also little programs

• They select records for one (or more) tables
• They can perform some basic computations
• counting, averaging, etc.
• computing new fields (e.g., total compensation
  base bonus)
• They can sort data and make it ready to display\

7
Making a query
Click here to create a new query
8
Example How many books?

• First you pick which tables or queries you will
  need to draw on. Note that you can query
  existing queries -- they are JUST LIKE TABLES.

To answer this question, I only need the
activities table
9
Add necessary tables
10
Add necessary fields
Drag fieldsto add
11
Switch views to see results
Click here to switch to datasheet view see
what the query retrieved. Do this FREQUENTLY.
12
We retrieved all the activities!

• How do we limit the selection to just the books?

13
Use Selection Critieria

• Books have CategoryID 2

When you enter a value in the Criteria,
query selects only records that match the
criteria
14
New results only books

• Note that you can edit these values here, just
  like a table.
• A query returns a recordset that is just like a
  table

15
Give the query a good name

• Naming is VERY important so you can find things
  later. You will have LOTS of queries and you
  need to tell them apart.

16
Next question all activities to be shared
externally

• The PR folks might need this info on a regular
  basis

Results in
Use selection criteria here...
17
Next question Who did these activities?

• Our queries so far include only one table
• But the information about who did the activity is
  in another table. How can we retrive it? Need
  to add another table

18
Add the people table...
Results in
You can sort the results, too
19
To get just Cookies activities?
Results in
Selection criteria limit the results to the
records you want
20
Expressions compute new fields

• Often, the data you need is a combination of one
  or more fields
• LastName, FirstName
• Total Pay base bonus
• Arithmetic expressions (, -, , /)
• Expr1 (NumField1 NumField2) NumField3
• Combining strings (use the ampersand )
• Expr2 table!TextField3 table!TextField4
• Add spaces and commas explicitly in double quotes

21
Example LastName, FirstName
We want a single field with both parts of the
name in it...
22
Build an expression
Right click on the field, then select the
build option
23
The Expression Builder
You can just type in your expression, or use the
boxes at the bottom
24
Combining first last names
The same tool is used for reports and elsewhere
in MS Access. Expressions are covered in detail
in your book and in the HR/Vantage tutorial.
25
The expression is a new field
26
HR/Vantage tutorial

• The instructions do not exactly match the
  software!
• When the instructions refer to the Person and
  Emp tables, you need to add the tPerson and
  tEmp tables.
• Similarly for other tables.
• This is an extensive tutorial in a real HR
  database. It is worth taking the time to do
  carefully and thoroughly.