Getting started with queries

1. 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

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 Cookie’s 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.