"Totals" button in query - what is it used for?

[George Matthew Dareos]

While trying to figure out a vacationing coworker's query, I find that
he's got a "Total" row in the quey grid, and I discovered that by
clicking on what looks like the greek letter sigma on the toolbar,
that it toggles this row on/off.

Suboptions are Group By, Sum, Avg, etc. but I can't tell from
executing the query that they do anything.

All his "Total" values are "Group By."

Thanks for any insight.

 

[George Nicholson]

A "Totals" query is a Select query that is doing some aggregate calculations
on the data selected: Sum/Average/Count the number of records that match
this criteria, etc.

If everything in the "Total" line is "GroupBy", then the query is not doing
any aggregate calculations, it is just selecting data. Its the actual
presence of an aggregate function, not the presence of the "Total" grid
line, thats important.

HTH,

 

[Ken Snell \(MVP\)]

A Totals query is used to get aggregate values from fields in the query's
table(s). The aggregate functions are Sum, Count, First, Last, Avg, etc. The
Group By tells the query which field(s) are to be used for distinctly
aggregating the data.

If the query is using only the Group By setting for all the fields, then
it's very possible that the query does not need to be a Totals query,
assuming that all the table fields are in the query. However, there are
times when this can be used to not select duplicate records from the query's
data source.

 

[Guest]

If every field is Group By it rolls it all up like this --
XYZ ABC DEF 4
XYZ ABC DEF 5
XYZ CBS DEF 3
XYZ FEX DEF 7
XYZ ABC SWA 3
To this ---
XYZ ABC DEF 9
XYZ ABC SWA 3
XYZ CBS DEF 3
XYZ FEX DEF 7

If you have a Sum it totals at that level.

A WHERE is not displayed.

 

[George Matthew Dareos]

Thanks very much -- I'm impressed with everyone's helpful attitude!

I see that today I've learned another tool that could save me from
having to always write a report to get some quick totals.

Thanks again!