Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-18-2018, 12:03 PM   #1
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,959
Thanks: 92
Thanked 1,709 Times in 1,586 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Totals row in Access tables & queries

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/art...6-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'
Attached Images
File Type: png Capture.PNG (14.4 KB, 154 views)

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 06-18-2018 at 01:07 PM.
isladogs is offline   Reply With Quote
Old 06-18-2018, 09:02 PM   #2
bob fitz
Newly Registered User
 
Join Date: May 2011
Location: Essex England
Posts: 3,451
Thanks: 29
Thanked 670 Times in 654 Posts
bob fitz has a spectacular aura about bob fitz has a spectacular aura about bob fitz has a spectacular aura about
Re: Totals row in Access tables & queries

Quote:
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
__________________
Bob Fitzpatrick
Microsoft Community Contributor Award (2011)
If this helped, please click the scales at the bottom left of this posting.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Many thanks.


I have never failed. I have found a thousand ways that do not work!
bob fitz is offline   Reply With Quote
Old 06-18-2018, 09:45 PM   #3
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,433
Thanks: 13
Thanked 1,419 Times in 1,353 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Totals row in Access tables & queries

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

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 06-19-2018, 12:53 AM   #4
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,959
Thanks: 92
Thanked 1,709 Times in 1,586 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Totals row in Access tables & queries

Quote:
Originally Posted by bob fitz View Post
I thought the same was true of multi-value fields but they are there
Yup along with attachment fields & calculated fields .... Ugh

Quote:
Originally Posted by Pat Hartman View Post
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'
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 06-19-2018, 01:09 AM   #5
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,704
Thanks: 138
Thanked 1,535 Times in 1,507 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Totals row in Access tables & queries

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.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 06-19-2018, 01:30 AM   #6
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,289
Thanks: 78
Thanked 1,409 Times in 1,329 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Totals row in Access tables & queries

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.
Galaxiom is offline   Reply With Quote
Old 06-19-2018, 09:02 AM   #7
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,433
Thanks: 13
Thanked 1,419 Times in 1,353 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Totals row in Access tables & queries

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

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 06-21-2018, 11:35 AM   #8
RogerCooper
Newly Registered User
 
Join Date: Jul 2014
Posts: 130
Thanks: 4
Thanked 11 Times in 10 Posts
RogerCooper is on a distinguished road
Re: Totals row in Access tables & queries

I have totals at the bottom of query you are printing is sometimes handy.
RogerCooper is offline   Reply With Quote
Old 06-21-2018, 12:04 PM   #9
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,959
Thanks: 92
Thanked 1,709 Times in 1,586 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Totals row in Access tables & queries

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 06-21-2018, 07:38 PM   #10
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,433
Thanks: 13
Thanked 1,419 Times in 1,353 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Totals row in Access tables & queries

Quote:
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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 06-22-2018, 12:53 AM   #11
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,959
Thanks: 92
Thanked 1,709 Times in 1,586 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Totals row in Access tables & queries

Quote:
Originally Posted by Pat Hartman View Post
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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 06-22-2018, 09:53 AM   #12
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,433
Thanks: 13
Thanked 1,419 Times in 1,353 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Totals row in Access tables & queries

Quote:
I still see no use for this in a distributed application
Just making sure because you clearly have your eyes closed.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 06-22-2018, 10:47 AM   #13
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,959
Thanks: 92
Thanked 1,709 Times in 1,586 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Totals row in Access tables & queries

Quote:
Originally Posted by Pat Hartman View Post
Just making sure because you clearly have your eyes closed.
Did they teach you that line at charm school?
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 06-22-2018, 12:37 PM   #14
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,433
Thanks: 13
Thanked 1,419 Times in 1,353 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Totals row in Access tables & queries

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.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Tags
totals row in tables

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
TIP: How to fill down in Access tables, queries and forms isladogs Code Repository 2 01-15-2018 02:31 PM
Question Access Unhidden Tables/Queries roseyo General 0 08-23-2012 07:04 AM
How to get Totals in Access Queries amunafc Queries 2 01-14-2010 12:42 AM
A2003: disallow access to Tables, Queries, etc. bulrush Modules & VBA 3 12-01-2009 09:31 AM
Access tables behaving like queries!!! BenJones Tables 1 02-06-2007 02:59 AM




All times are GMT -8. The time now is 05:29 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World