none
Not able to Display Column Totals (Totals Row) in MS Access for Linked Table from SQL Server RRS feed

  • Question

  • When I have the Totals Row showing in Datasheet view of a Linked Table from SQL Server, there is no way I can see how to actually set any Table Type for any column totals (e.g., Sum on a column that is a number).  [Update: SOLVED, was simply a case of impatience.  See my comment reply below.]

    This feature is described in this article:

    https://support.office.com/en-us/article/display-column-totals-in-a-datasheet-using-a-totals-row-8f1f89c4-7f86-4113-a836-291ac3ea446e

    The article doesn't mention anything about this feature not supported for Linked Tables.

    This does work for Linked Tables from an Access database, just not for a Linked Table from a SQL Server table

    Is this an ODBC limitation, ... or is it a feature just not supported in MS Access for SQL Server Linked Tables?  (e.g., from Office Pro Plus 2016).


    [Update: See my reply below.]
    Friday, September 6, 2019 8:39 PM

Answers

  • SOLVED!!!

    Ok, this was due to PEBKAC

    The linked table was huuuge, and I simply was impatient, assuming I could see a response when clicking where I would get the drop-down to choose the type (e.g., SUM, or COUNT, etc.).   I hadn't realized that Access was still loading the table, and thus wasn't showing me the drop-down where I could choose the type (e.g., SUM, or COUNT, etc.).

    I found this was occurring after I added a WHERE filter on the query, and then I could suddenly choose the type in the Totals column.   That was, of course, because the filter limited the rows in the query to just a small quantity ... where Access completed loading quickly and then presented the drop-down in the column clicked in the Totals row.

    I can't be the only person who has run into this, so hopefully my explanation here of why this is happening will help someone else.


    If this does occur for you, and you'ld like Microsoft Access developers to look into remedying this, ... I have a suggestion on the MS Access Uservoice tool. 
    • Marked as answer by Stephen Gornick Wednesday, April 29, 2020 1:32 AM
    • Edited by Stephen Gornick Wednesday, April 29, 2020 1:59 AM Add link to MS Access Uservoice suggestion.
    Wednesday, April 29, 2020 1:22 AM

All replies

  • Hi sgornick,

     

    Thank you for your issue.

     

    Per your description , would you like to get a total row in SQL Server ? As I know , in SQL Server you can try to use SUM()/COUNT()/MAX()/MIN() and so on to get your expected result.

     

    Here is an example.

     
    declare @table table(A int, B int )
    insert into @table values (1,2),(3,4)
    
    select * from @table
    /*
    A           B
    ----------- -----------
    1           2
    3           4
    */
    
    select cast(row_number()over(order by (select 1)) as varchar(10)) rn,* from @table
    union all 
    select 'Tatal', sum(A) A, sum(B) B from @table
    /*
    rn         A           B
    ---------- ----------- -----------
    1          1           2
    2          3           4
    Tatal      4           6
    */
    
    

    Hope it will help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, September 9, 2019 6:12 AM
  • Hi Rachel,

    Thank you for the excellent suggestion.   An Access Query with a totals row append by using a UNION ALL will have to do, for now.

    I still am holding back from marking this question as being answered as I don't know for sure yet that support for the Totals Row in Datasheet View is not possible with SQL Server Linked Tables (or any ODBC Linked Table, I presume).   Nobody else has confirmed they get the same behavior, but I've done this from two different systems, both being MS Access from Office Pro 2016, and get the same behavior on both so I'm presuming it's not just me experiencing this.


    Friday, September 13, 2019 3:05 PM
  • Hi,

    If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, September 16, 2019 8:48 AM
  • I have the same problem with a linked table to a dbase database (which is used by Sage book keeping software). I can't select any aggregate for the total row.
    Wednesday, April 22, 2020 7:52 PM
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 28, 2020 8:36 AM
  • SOLVED!!!

    Ok, this was due to PEBKAC

    The linked table was huuuge, and I simply was impatient, assuming I could see a response when clicking where I would get the drop-down to choose the type (e.g., SUM, or COUNT, etc.).   I hadn't realized that Access was still loading the table, and thus wasn't showing me the drop-down where I could choose the type (e.g., SUM, or COUNT, etc.).

    I found this was occurring after I added a WHERE filter on the query, and then I could suddenly choose the type in the Totals column.   That was, of course, because the filter limited the rows in the query to just a small quantity ... where Access completed loading quickly and then presented the drop-down in the column clicked in the Totals row.

    I can't be the only person who has run into this, so hopefully my explanation here of why this is happening will help someone else.


    If this does occur for you, and you'ld like Microsoft Access developers to look into remedying this, ... I have a suggestion on the MS Access Uservoice tool. 
    • Marked as answer by Stephen Gornick Wednesday, April 29, 2020 1:32 AM
    • Edited by Stephen Gornick Wednesday, April 29, 2020 1:59 AM Add link to MS Access Uservoice suggestion.
    Wednesday, April 29, 2020 1:22 AM
  • I have the same problem with a linked table to a dbase database (which is used by Sage book keeping software). I can't select any aggregate for the total row.

    Your comment prompted me to take another look, and sure enough I found the solution.

    If your linked table has many rows, you won't be able to get the drop-down selector until Access has retrieved all the rows.

    What I did was hit the  ">|" button for "Last row" (in the Record navigation tool, bottom left corner).  That caused Access to load all rows.  Then when I clicked on the column I wanted, from the Total row, the drop down selector with Sum, Average, Count, etc., shows right away.

    Hope that solves your issue!


    Wednesday, April 29, 2020 1:31 AM