260 likes | 277 Views
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. Getting started with queries. Retrieving & sorting data. To make data useful, you need to get it back out of the database
E N D
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... Getting started with queries
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 fields Drag fieldsto 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.
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.