none
Access 2010 - display total rows in form (datasheet format)

    Pertanyaan

  • Is there a way to display the total rows in datasheet form?  I know I can do it in split form but each time i place the split form in a subform, the datasheet is displayed on the bottom.   I set it to display on top but it doesnt work while in subform. 

    Any help is appreciated.  Thanks,

    25 Oktober 2012 21:04

Jawaban

  • Hi AzuDaioh,

    Sorry about the late response.

    As far as I can see, it is still OK to display the total row in the datasheet form. 

    >> I set it to display on top but it doesnt work while in subform.  

    But I'm not sure about this situation. Do you want to display the total row on the top of the subform?

    Best regards,


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us

    • Ditandai sebagai Jawaban oleh AzuDaioh 07 Nopember 2012 19:50
    07 Nopember 2012 8:42
    Moderator

Semua Balasan

  • Why not just set the form to Continuous view and add a totals text box in the form's footer section?  With a little fiddling around, it can look like a datasheet form and do other things too.
    25 Oktober 2012 23:15
  • That's what I currently have but just wondering if it's possible in datasheet as well.  Thanks,

    31 Oktober 2012 17:54
  • Hi AzuDaioh,

    Sorry about the late response.

    As far as I can see, it is still OK to display the total row in the datasheet form. 

    >> I set it to display on top but it doesnt work while in subform.  

    But I'm not sure about this situation. Do you want to display the total row on the top of the subform?

    Best regards,


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us

    • Ditandai sebagai Jawaban oleh AzuDaioh 07 Nopember 2012 19:50
    07 Nopember 2012 8:42
    Moderator
  • Hi AzuDaioh,

    Yes it is possible, but you have to work for it:

    For example: if you have a form whose recordsource is a query like this (or a table with these fields):

                       ---------------------------------------------------------------------------
                       SELECT Category, [Name], Amount FROM NamesAndAmounts 
                       ---------------------------------------------------------------------------

    Then you can add a totals row at the bottom by making a query with this SQL and binding the form to it:

                       ----------------------------------------------------------------
                       SELECT [Name], Amount, 0 AS OrderBy FROM NamesAndAmounts
                       UNION
                       SELECT "Total", Sum(Amount), 1 FROM NamesAndAmounts
                       ORDER BY OrderBy, [Name]
                       ----------------------------------------------------------------

    The OrderBy field allows you to force the totals row to the bottom, by including it in the ORDER BY clause before any other fields you want to sort by.

    This method can also be used to add subtotals rows as well:

                       ----------------------------------------------------------------
                       SELECT Category, [Name], Amount, 0 AS SubOrderBy, 0 AS TotOrderBy
                       FROM NamesAndAmounts

                       UNION

                       SELECT Category, "SubTotal", Sum(Amount), 1, 0
                       FROM NamesAndAmounts
                       GROUP BY Category   
              
                       UNION

                       SELECT "Total", NULL, Sum(Amount), 0, 1
                       FROM NamesAndAmounts

                       ORDER BY TotOrderBy, Category, SubOrderBy, [Name]
                       ----------------------------------------------------------------

    The ORDER BY clause needs two order by fields now, to interleave the subtotals rows into the data and put the totals row at the bottom.

    I produced the table below and the query output below that to show what i mean:

    NamesAndAmounts

    Category

    Name

    Amount

    Us

    Me

    3

    Us

    You

    4

    Them

    He

    5

    Them

    She

    6

    Them

    It

    7

    Query Output

    Category

    Name

    Amount

    SubOrderBy

    TotOrderBy

    Them

    He

    5

    0

    0

    Them

    It

    7

    0

    0

    Them

    She

    6

    0

    0

    Them

    SubTotal

    18

    1

    0

    Us

    Me

    3

    0

    0

    Us

    You

    4

    0

    0

    Us

    SubTotal

    7

    1

    0

    Total

        

         25

    0

    1

    You do not have to bring the OrderBy fields on to the datasheet of course, just don't include textboxes for those fields.

    Also, if you base the form on this query, it will be a non-updatable form, meaning you will not be able to edit data in cells. But you could dump the query output into a temp table and bind the form to the table. But then you would also have to write the code to allow the user to write edits from the temp table back to the main table. But this would have the advantage of holding off the final decision to do that write-back until the user clicks on a "commit" button, and there could be a discard button to undo edits before they are committed.

    Lots of work, sorry, but hope it helps,

    Nick.

    07 Nopember 2012 12:33
  • Thank you guys for the responses. I'm not sure what I did initially for the 'total' button from the ribbon to be disabled but when I re-created the subform, the 'total' button was enabled. The only thing I did different was I saved the subform as a separate 'form'.

    07 Nopember 2012 20:06