Queries and Reports
|
At the end of this tutorial you should know:
|
|
Why and what are queries?
|
When we are looking through our students database from Lesson 1 we notice that we can use filters to search through our data
This meant that it was easy to find all limit the amount of data to what we want to see. But what if we only wanted some of the information? What if we didn't want their enrollment information. A query creates a separate table with only the information you want spread across multiple tables. A query also allows us to perform a lookup, so that we can hide things such as number ID's Numbers don't work well with humans, words do :-) |
|
Activity - Student Database
|
Gary the school principal in Cambridge needs to contact all the students who live in the Hamilton Area. All he wants in front of him is the students First name, Last Name and phone number. He only wants to call students that are year 11 and above. He also doesn't want any students that are not enrolled for next year. He doesn't want any of this information to be shown. 1.) Open the Student Database that can be found in the first lesson. 2.) Click "Create" then "Query Design" (you can use the wizard but there will be times where it is unavailable so it is better if you don't). 3.) Add the tables "Students" "Areas" Year Levels" to the query Take note of their relationships 4.) Add the fields you need... Keep in mind you will need fields that are not necessarily shown 5.) Using our amazing programming skills we can program in values. With help from the picture on the right try to adjust the criteria so that only the results you want show up. 6.) Create Run! 7.) Save your query and notice it come up on the left 
|
|
Advanced Queries
|
Download the database on the right!
It is a meeting and employee database. Read through this link: Create 2 queries 1.) One query that shows any future meetings for one employee 2.) Another query that shows any past meetings by the last 2 months(60 days). If you figure it out then tell 3 other people how you did it and claim bragging rights. |
Picture: Hint for part 2
| ||
Creating Reports
|
Using the same database as before we are going to create a report that shows all meetings by employee.
1.) Under create find "report wizard" 2.) Select the different fields that you want to create a container for 3.) Click Next 
6.) Click through until you get to the sorting window... sort by meeting date.
You will probably notice that the dates are showing as #### this is because they don't have enough room to be shown
To change this go into design view and drag and adjust the widths. Make other adjustments in this view like adding spaces etc. |
4.) We would like to organize meetings by employee so select that one.
5.) Click Next 
7.) Choose any layout you wish, and then label your report "Employee Meetings"
8.) Go through to the finish 
|
Reports and Queries
|
Rather then use the tables will which show all information we only want to show upcoming meetings.
To do this use a query that you created in your previous activity. Task: Create a report based off your previous query that will |
|
Extra Prisoners Task
|
Open up the prisoners database you completed two lessons ago
Create a report by crime with the criminals listed underneath it |
|
Best friends that borrow your controllers and don't give them back
|
Enter 4 more instances of people borrowing your stuff Make sure one entry is: * A week ago * A month ago * A year ago Make sure each of them has not returned an item. Create a query that only shows people that have recently borrowed your stuff. (last 30 days). It should only show the persons name and the item name. Create another query showing people that have borrowed stuff from over a year ago and not returned it. Create a report for each query. (Try include a picture) |
|
