Advanced Database – Group information in a query using functions.
Add a ‘Staff Number’ field to the Job Hours table and fill in the Staff Numbers from the jobs table.
We could have used a join to link the Staff Number field between the two tables, but grouping information in a query doesn’t work with joined tables.
Create a new query and add the Job Hours table.
Select to display the Staff Number field and select ‘Group’ function i.e. where there is more than one record containing a certain value it will group them into one.
Select to display the Alias field Total Hours (Normal+Overtime) and select ‘Sum’. This will take each staff number and find the sum of the Total Hours field for each. Press F5.
The query table returns the sum of the Total Hours field for each staff number, grouped together.
Try changing the function in the second column to ‘Average’ and press F5.
The query table now returns the average number of total hours each staff member worked.
Try changing the function in the second column to ‘Maximum’
The query table now returns the maximum number of total hours each staff member worked.
If we had used the ‘Minimum’ function it would have returned the least number total number of hours worked for each staff number.
For the last example, change the Alias to ‘Number of Jobs’ and change the function to ‘Count’. Press F5.
The query table now returns the total number of jobs each staff member worked.
The Count function, counts each unique record that exists for each Staff Number and returns the total value.
