The Access Wizard Newsletter
Tips, Tricks, and Traps for Access Users and Developers.

A Hidden Jewel: Totals in Queries

Take a look at the picture below. No doubt you see a jungle scene and a tiger; but there is a hidden tiger in the picture as well. Do you see it?

The hidden tiger is not all obvious, but it is there. (If you need to find out immediately where the tiger is hidden, scroll to the end of this newsletter.)
Like the hidden tiger in the picture above, once you have seen something that is hidden among the clutter, you will always see it. Like a hidden tiger, there are hidden treasures scattered throughout Access 2010/2007. The one that we'll talk about this month is a handy little tool that is hidden in the clutter of the query screen:  Totals.

The Deception

When you create a query, you will see the summation sign as shown in the picture below. If you click the summation sign, you get the option of creating a summary query where you can group results and get summary statistics. Although you could use this to get the total for the query by grouping, and then selecting sum for the column of interest, there is a much easier way to get to total for a query. 


The Hidden Jewel

Create a regular select query and run it.
In the Home tab, you will see a tab called Records. On that tab, you will see the same summary symbol you saw in design view. However, this one is a very different thing. Rather than creating a summary query, it allows you to get a summary statistic on a column or columns you select. 

Once you click the summary sign in the ribbon, a total appears at the bottom of your screen. And, to the right of that, you can select all the typical summary information for that particular column, and will get exactly what you would expect. 
This feature is a real timesaver. It easily allows you to get a summary statistic on any column on the fly. Once I learned about this, I started using it regularly and found that it saved me all sorts of time. I think you will find the same.

The Hidden Tiger

Take a closer look at the tiger. Start at the shoulder and you should see the word “The.” Continue to the right and you will see the word the “Hidden” on the tiger's body. Finally, read down rear leg and you'll see the word “Tiger.”

This is one of those many cases where something was there all the time, but was difficult to see because you didn't know to look for it. Like the hidden tiger, now you will see it all the time, just as you now can see one of the many jewels buried in Access: summation within a select query.


Tip of the Month: 
Shameless Self-Promotion Department

On Wednesday, March 13, I will be giving a presentation to the Connecticut Access Users Group (CTAUG) in Farmington, Conn. CTAUG is a group of Professional Access Developers that strives to educate and support developers and users of Microsoft's Access database product.

My talk will have two components:

1.      Tips, tricks and methods to develop efficiently.

2.      An analysis of the structure, logic, and code of the Reporting Engine, which is a part of all my applications. This engine allows users to find reports easily and to effortlessly choose options to get the information they want.

The meeting starts at 5:30 PM with networking. The presentation typically starts between 6:15 PM and  6:30 PM. Full details and directions are available on the CTAUG website at


Quick Links:

Custom Software Home

Access Wizard Archives

Our Services

phone: 978-392-3462

© Copyright Custom Software
All Rights Reserved | Westford, MA 01886