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
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.
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
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.