Databases

Aggregate Queries

Access can perform several powerful aggregate functions on a database when we use a query to find information.

Most of the aggregate functions are designed to be used with numerical data, and our timetable database does not contain any data that we can produce meaningful results from when we use the aggregate functions.

Lets consider another database when the number of goals scored by each team in the games they play are stored in a table like this;-

It would be useful to find out the total number of goals, the average number of goals, the highest number of goals and the lowest number of goals scored by each team.

The main functions that we have available are:-

Sum - this adds up the value of all of the fields that meet our criteria.

Min - this returns the lowest value of the field

Max - the exact opposite of min, gives us the maximum value of a field

Avg - calculates the average value of a field.

This diagram will explain how the aggregate functions are used.

This design of query will produce these results:-

Check back with the data at the top of the page, has it worked?

Now that we are masters of the database query, we can look at how we display the results of our queries.

Click here to find out about forms

Click here to return to the top of the page