databasedev.co.uk - database solutions and downloads for microsoft access

Dashboard Builder for Microsoft Access
Need to see key data from your Access database in a single dashboard? Tired of creating query after query to really understand your Access data? Dashboard Builder for Microsoft Access is an easy to use tool to create powerful dashboards in minutes…without being a SQL wizard. Free trial available

SQL and MS Access Functions

Aggregate Functions in SQL and Microsoft Access:

With SQL there are many functions available that allow you to perform or manipulate calculations on your data. These functions can be used as part of the SQL SELECT statement.

Aggregate Functions

Aggregate functions allow you to perform calculations on multiple rows of data, but will only return a single value in the response. Some of the aggregate functions available to us include:

  • AVG( )
  • COUNT( )
  • MIN( )
  • MAX( )
  • SUM( )

If we have the following table of data:

tblTitles
TitleID Title Price
1 Introduction to PCs 9.99
2 Introduction to Windows 9.99
3 Introduction to the Internet 9.99
4 Microsoft Word Advanced 12.50
5 Microsoft Excel Advanced 12.50
6 Microsoft Access Advanced 12.50
7 VB.NET Windows-Based Applications  
8 VB.NET Web Applications 18.99
9 VB.NET XML Web Services and Server Components 18.99
10 Solution Architecture 21.75
11 SQL Server 2000 24.99

Now using aggregate functions, we can answer some of the following example questions:

"What is the average price of a book in our collection?"
"How many books do we have in the collection?"
"What is the total cost of the inventory that we have available?"

If, for example, we want to calculate the average price of a book in the Titles table, we can use the following query:

SELECT AVG(Price)
FROM tblTitles;

What you will need to be aware of however, is that if the table's column permits NULL values (where no data is entered into the column), the result of the aggregate function calculation might well be inaccurate.

In our example, we can see how the inclusion of NULL values (TitleID # 7 in our Titles table) in the data will cause inaccuracies.

Our calculation gives us a result of the Average Price of a book in the Titles table as being approx = 15.22 (152.19/10 Titles), however there are actually 11 Titles listed in the table, but one has no value associated with it (NULL value). If the calculation included this title, the average price would be approx = 13.83 (152.19/11 titles)

Another example of problems that may be caused by allowing Nulls in the table is when using other aggregate functions such as MIN( ) or MAX( )

You may think that using the MIN( ) function to find the lowest valued book in the titles table would return a book with no value. We would use the query of:

SELECT MIN(Price)
FROM tblTitles;

However, as SQL ignores null values, you will get the lowest priced book that contains an actual value in the price column of the table. In this case, our query would return the value of 9.99

With the exception of COUNT (*), all of the other aggregate functions will disregard the NULL values. You would use the COUNT (*) function to return the amount of all the rows in the table, including those rows that may contain NULL values. A simple example of this would be to find "How many books do we have in the collection?"

We use the following query to answer our question:

SELECT COUNT(*)
FROM tblTitles;

This will return a value of 11, as the total amount of rows contained in out Titles table.