Transcript Getting started

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...
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
ActRep example
• I added a three records to the “people’ table
ActRep example
• … and I added a bunch of records to the activities table (by
copy and “paste append”, so they look similar…)
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?
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\
Making a query
Click here to create
a new query
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
Add necessary tables
Add necessary fields
Drag
fields
to add
Switch views to see results
Click here to switch
to datasheet view &
see what the
query retrieved.
Do this FREQUENTLY.
We retrieved all the activities!
• How do we limit the selection to just the books?
Use Selection Critieria
• Books have CategoryID = 2
When you enter
a value in
the “Criteria”,
query selects
only records
that match
the criteria
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
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.
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...
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:
Add the people table...
Results in
You can sort
the results, too
To get just Cookie’s activities?
Results in
Selection criteria limit the
results to the records you want
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
Example: LastName, FirstName
We want a single field with
both parts of the name in it...
Build an expression
Right click
on the field,
then select the
“build” option
The Expression Builder
You can just type in your expression, or use the boxes at
the bottom
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.
The expression is a new field
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.