Access Homework 3A – Totals Queries Practice (Pages 9-12 in Guide to creating queries)Concepts: Totals queries, count on Primary key, AVG, SUM, MAX,MIN. WHERE
Notes: work methodically – checking your work as you go. If you have too many rows, consider if you have the correct field: can the field be grouped? Or are there too many distinct values? If so, should you WHERE on that field?
Qry 3a-1: For all teachers in the table, Display as a group where you Count the number of teachers, as well as Sum and Average their [salaries]. (1 row). Add [tenure]. Show only teachers who do not have tenure. 1 row
Qry 3a-2: Write query that groups by [teaching route]. (2 groups). Now add [county] 4 groups. Count the number of rows in each group and show the min/max for each [salary]
Qry 3a-3: Display groups by [certificate]. 7 rows. Average the [salary], [experience total] and [FTE] values. Count the number in each group. Still 7 rows. Format FTE as % and Experience as 2 dec.
Qry 3a-4: Group records by [district], 35 rows. Add tenure group. 65 rows. Count records in each group, Average, min and max salaries. 65 total rows.
See next page
Qry 3a-5: Display those records for teachers who were [hired] after 2014.(246 rows) Make them 1 group by using the where clause, but before running add id number to Count the records in the group. 1 row. Add teaching route – now 2 groups.
Qry 3a-6: Display records for teachers who make more than $100K in [Salary].(242 rows). And who are from [district] of Egg Harbor Twp. (11 records) Make the rows one group and average [years of total experience]. 1 row
Qry 3a-7: Query the teachers whose [primary job] is Elementary – kindergarten-8 grade (Check the spelling of the values!) (375 rows). Group and display Count of group. Max their [salary]. Add non tenured teachers to the group – or put another way – do not show the teachers with [tenure]. Another way of reading this statistic: There are 142 Elementary Kindergarten-8 Grade untenured employees, of which the highest paid receives a salary of $112,676.00.
Qry 3a-8: Group the teachers based on [FTE].19 rows Add [county] to the groupings. Show Atlantic county only. 15 rows Count the rows in each group. Add criteria for [FTE], for only instructors over 75% - 2 rows but show this new group as 1 row.
Qry 3a-9: Write a query that displays the rows for instructors that do NOT teach general ed.[subcategory], 590 rows. Count ids, Sum,Avg, Min, and MAX salaries across rest of groups – 4 rows
Qry 3a-10: Write query that displays the teachers [hired] in 2010 and 2011. (140 rows). Roll them up into 1 count of 140. Add [primary job] and sort ASC. Save your query now. Show only the counts of groups that are 10 and over. (2 groups)