Totals row in Access tables & queries

[isladogs]

After 20 years of using Access, it amazes me how often I come across features that I've never seen before

Whilst looking at various datasheets (both tables/queries) in a client database recently, I noticed several had a totals row - something I had never seen before.
If this is used, it is displayed at the bottom in bold
As you can see from the screenshot, there are various options to choose from

Apparently this has been available since A2007 and can also be used in the datasheet part of a split form

This link explains how to set it up: https://support.office.com/en-us/article/display-column-totals-in-a-datasheet-using-a-totals-row-8f1f89c4-7f86-4113-a836-291ac3ea446e

However there are certain things to consider
a) only one label is possible so if you use e.g. count for one field and sum for the others, these aren't marked in any way
b) totals rows are not transferred to a query from the table.
c) Similarly the totals row isn't carried forward to a report

Now it may be everyone else knew about this apart from me
However, as its good practice NEVER to allow end users to work with tables or queries direct, can anyone tell me any real use for this 'feature'

 

[bob fitz]

However, as its good practice NEVER to allow end users to work with tables or queries direct, can anyone tell me any real use for this 'feature'

I thought the same was true of multi-value fields but they are there

 

[Pat Hartman]

can anyone tell me any real use for this 'feature'

Use it in DS view subforms. I use it but find it to be flaky so if I need totals, I'm more likely to just make a total row in the unbound main form. The problem with that of course is it gets misaligned if the users muck with the width or column order of the subform.

 

[isladogs]

I thought the same was true of multi-value fields but they are there

Yup along with attachment fields & calculated fields .... Ugh

Use it in DS view subforms. I use it but find it to be flaky so if I need totals, I'm more likely to just make a total row in the unbound main form. The problem with that of course is it gets misaligned if the users muck with the width or column order of the subform.

Not sure I follow this. If you move a datasheet field, the totals item moves with it. Ditto if you change the width
Similarly if you filter the datasheet, the totals row is updated in line with the filter.

However, I can't personally see any possible use for this feature.
To me its another curiosity to file away as 'Just because its there, doesn't mean anyone should use it'

 

[Minty]

I frequently let users run queries in a read only mode for quick lookups.

If they need to "play" with the data or analyze more closely they can tick a box and get a formatted excel export of it.

 

[Galaxiom]

I wouldn't provide them for a user but they can occasionally be handy when analysing the extent of values returned by a query during development.

 

[Pat Hartman]

Not sure I follow this. If you move a datasheet field, the totals item moves with it. Ditto if you change the width

I was referring to what happens when I put total controls on the MAIN form that sum the data in the subform.

If you don't use subforms in DS view, I agree, you would never use this feature because no user ever sees queries or tables. But, where I have used it, the users like it.

 

[RogerCooper]

I have totals at the bottom of query you are printing is sometimes handy.

 

[isladogs]

Hi Roger
I can see it could be useful during development work (as Galaxiom also mentioned).
However my original point stands:
As end users should not be able to access queries, I still see no use for this in a distributed application

 

[Pat Hartman]

As end users should not be able to access queries, I still see no use for this in a distributed application

Did you miss my comment that they are used on SUBFORMS in DS view. That isn't a query, it is a form with all the events of a form.

 

[isladogs]

Did you miss my comment that they are used on SUBFORMS in DS view. That isn't a query, it is a form with all the events of a form.

Yes I saw it, and if you look back, I replied to it & you replied to that response as well....

However:
1. I hardly ever use datasheet view in forms / subforms
2. The totals row isn't carried forward from the table or query to the form. It has to be re-created manually as I mentioned in post #1 (though I referred to a report rather than a form)
3. I still think the lack of individual labels for each 'total' is unhelpful as one could be Sum, another Max & yet another Standard Deviation etc

Speaking of 'did you miss my comment' .....
Did you miss my reply to your comment in post 21 of this thread: Display Selected Controls

 

[Pat Hartman]

I still see no use for this in a distributed application

Just making sure because you clearly have your eyes closed.

 

[isladogs]

Just making sure because you clearly have your eyes closed.

Did they teach you that line at charm school?

 

[Pat Hartman]

Sorry. It is difficult to be amusing when you forget the emoji with the wink. But, I'm glad to see you still have a sense of humor about you.