Totals Queries in MS Access

A Totals Query summarizes data instead of displaying every individual record. For example, a coaching institute can use a Totals Query to calculate total fees, average fees, or the number of students in each course.

Example: Students Table

Using a Totals Query, you can calculate:

• Total fees collected or recorded.
• Average fees per student.
• Total number of student records.
• Highest and lowest fees.
• Total fees for each course.

Follow these steps to create a Totals Query:

1. Open your MS Access database.
2. Click the Create tab.
3. Select Query Design.
4. Add the Students table.
5. Add the fields you want to use in the query.
6. Click the Totals (Σ) button on the Design tab.
7. A Total row will appear in the query design grid.
8. Select the required calculation from the Total row.
9. Click Run (!) to view the results.
10. Save the query with a meaningful name.

The SUM function calculates the total of numeric values in a field.

Example: Calculate Total Fees

SELECT Sum(Fees) AS TotalFees
FROM Students;

For the sample records, the total is 28000.

Result

The alias TotalFees gives a readable name to the calculated column.

The AVG function calculates the average of numeric values.

Example: Calculate Average Fees

SELECT Avg(Fees) AS AverageFees
FROM Students;

The total fees are 28000 for four records. Therefore, the average is 7000.

In MS Access SQL, the aggregate function is written as Avg().

The COUNT function counts non-Null values in a field. Counting a primary key is a useful way to count records when every record has a unique, non-Null ID.

Example: Count Student Records

SELECT Count(StudentID) AS TotalStudents
FROM Students;

If the Students table contains four records with StudentID values, the result is 4.

Count Students by Course

SELECT Course, Count(StudentID) AS TotalStudents
FROM Students
GROUP BY Course;

This query counts the students in each course.

The MIN function finds the smallest value, while MAX finds the largest value.

Example: Lowest Fee

SELECT Min(Fees) AS LowestFee
FROM Students;

The result is 4000.

Example: Highest Fee

SELECT Max(Fees) AS HighestFee
FROM Students;

The result is 10000.

Find Both Values in One Query

SELECT Min(Fees) AS LowestFee,
       Max(Fees) AS HighestFee
FROM Students;

This query returns the lowest and highest fee values in a single result row.

GROUP BY groups records that have the same value in a specified field. It is commonly used with aggregate functions to calculate a separate result for each group.

Example: Total Fees by Course

SELECT Course, Sum(Fees) AS TotalFees
FROM Students
GROUP BY Course;

Expected Result

Each course appears once, with the fees of its students added together.

Count Students and Calculate Average Fees by Course

SELECT Course,
       Count(StudentID) AS TotalStudents,
       Avg(Fees) AS AverageFees
FROM Students
GROUP BY Course;

This query returns the student count and average fees for each course.

You can filter records before calculating totals by adding a WHERE clause.

Example: Total Fees for ADCA Students

SELECT Sum(Fees) AS ADCAFees
FROM Students
WHERE Course = "ADCA";

This query calculates the total fees for records where the course is ADCA. Based on the sample data, the result is 18000.

Example: Total Fees Above 5000

SELECT Sum(Fees) AS TotalFees
FROM Students
WHERE Fees > 5000;

This query adds only those fee values that are greater than 5000.

The HAVING clause filters groups created by GROUP BY. It is useful when you want to display only groups that meet a summary condition.

Example: Courses with Total Fees Above 10000

SELECT Course, Sum(Fees) AS TotalFees
FROM Students
GROUP BY Course
HAVING Sum(Fees) > 10000;

This query displays only courses whose total fees are greater than 10000. In the sample data, ADCA qualifies with 18000, while Tally Prime does not qualify because its total is exactly 10000.

Create a Students table with StudentID, StudentName, Course, and Fees fields. Insert at least ten records across multiple courses.

Tasks

1. Calculate the total fees of all students.
2. Calculate the average fees.
3. Count all student records using StudentID.
4. Find the highest fee.
5. Find the lowest fee.
6. Calculate the total fees for each course.
7. Count the students in each course.
8. Calculate the average fees for each course.
9. Calculate total fees for ADCA students only.
10. Display courses whose total fees are greater than 10000.

Goal: Practice aggregate functions, GROUP BY, WHERE, and HAVING in MS Access.

1. What is a Totals Query?
2. What is the purpose of the SUM function?
3. What does the AVG function calculate?
4. What is the difference between COUNT and SUM?
5. What are the purposes of MIN and MAX?
6. What is the use of GROUP BY?
7. How does WHERE work with a Totals Query?
8. What is the difference between WHERE and HAVING?