Once you have a basic understanding of the basic Access SQL clauses, you need to learn about the more complicated clauses. Learning the two most common types will simplify your workload.
One of the primary draws of Microsoft Access is the ability to work with data quickly and easily. SQL gives you the tools you need to quickly and effectively create a query that will work with your data. The three basic SQL clauses retrieve data, but that isn’t always what you need.
Aggregate clauses pull the data and calculate it. Sometimes what you want is not the full results, but a subset. This is where the summary aggregate clauses are useful.
The two most commonly used aggregate summary clauses go further to manipulate your data. These two aggregate clauses provide a summary of your data so that you don’t have to plough through your data to get the bottom line.
- GROUP BY
Both of these clauses will give you just a summary instead of a data dump. This will make it a lot easier to get the bottom line.
Defining an Aggregate Clause
Aggregate clauses build on the basic SQL clause. There are seven primary types of aggregate functions:
- Avg Function
- Count Function
- First Function and Last Function
- Min Function and Max Function
- StDev Function and StDevP Function
- Sum Function
- Var Function and VarP Function
Each of these clauses has a particular purpose and use. It would take several articles to explain all of these, but you are not likely to use all of them. To understand them, check out the Access support page.
Aggregate clauses are always added as part of the SELECT clause. The following is an example that builds on the previous article’s example.
SELECT COUNT [First Name], Company
The result is a count of all the first names and companies listed. For other functions, like Avg, the query will calculate the average.
The summary aggregate clauses make it easier to review the data retrieved by the aggregated clauses.
How Summarized Aggregate Clauses Work
By themselves, aggregate clauses can provide far more data than you need. More often than not, you will add either the GROUP BY or HAVING clauses to organize the data results.
The primary purpose of the GROUP BY clause is to provide a list of all fields that you do not want to include in the aggregate function. If you want all fields, you do not need this clause. If you want to narrow down the list, this clause is the quickest way to do that.
The clause follows either the WHERE clause (when present) or the FROM clause (when there is no WHERE clause). If you would like to apply the First Name aggregate function and not the Company, you would use the following:
Group BY Company
The primary purpose of the HAVING clause is to group the values based on group criteria. You would use this if you want to limit results but want criteria added to the aggregate function you are using. This applies when you cannot add a WHERE clause, such as for averages.
The HAVING clause is more complicated than the GROUP BY clause because there are several contingencies. However, it is important to know so that you can more quickly review your data. For more details, check out the Access page on this complicated summary aggregate clause.
Managing Data Issues
If at any point it looks as though there are issues with your data, it is best to see if the problem goes beyond your SQL query. For problems that appear to be with the data, you may need Access data repair assistance. It is best to identify the problem early so that you can rectify the issue and resolve it quickly. This will help you keep your data cleaner.
Victor Ren is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including Excel file repair tool and word recovery tool. For more information visit www.datanumen.com
Leave a Reply