locked
Totals in a Datasheet RRS feed

  • Question

  • Hi,

    Am using an A2k App. New to Datasheets. I would like to create a totals row for some of the columns in a form datasheet. Is this possible?

    When the user filters the form datasheet on some criteria, is it also possible for the totals to be updated to show the relevant total values only.

    TIA

    Anand

    Friday, November 4, 2011 2:01 PM

Answers

  • Microsoft added this feature in Access 2007: open a datasheet, then click Totals in the Records group of the Home tab of the ribbon. It doesn't matter whether the database is in Access 2000, Access 2002-2003 or Access 2007-2010 format, as long as you're using Access 2007 (or later).

    In Access 2003 and before, there is no option to add totals to a datasheet, you'd have to create a report for that.


    Regards, Hans Vogelaar
    • Marked as answer by Bruce Song Wednesday, November 16, 2011 7:00 AM
    Friday, November 4, 2011 2:30 PM
  • Thank you for your responses. Continous forms with a text box in the footer does the job.

    However, I also tried using a union query which seems to work - the DS's recordsource is the first query in the union query. The other is a query that totals the first query and is unioned to it.

     

    This does work but I cant seem to get the total row to change when I filter the datasheet. It just shows the same total. Any Ideas?


    Yes:  don't do that.  <g>

    That approach won't work if you want the totals to automatically update when you filter the results.  That is because, when you use a union query, the totals row you created is part of the static, pre-filtering set of records.  Filtering that set of records won't update the totals.

    If you really have to do it via a union query as you describe, you'd have to set up the two contributing queries (whose results will be unioned) so that they refer to criteria controls on your form for filtering.  Then to filter the results you would modify those controls and then requery the form so that the union query is processed all over again.

     


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    • Marked as answer by Bruce Song Wednesday, November 16, 2011 7:00 AM
    Monday, November 7, 2011 6:25 PM

All replies

  • Microsoft added this feature in Access 2007: open a datasheet, then click Totals in the Records group of the Home tab of the ribbon. It doesn't matter whether the database is in Access 2000, Access 2002-2003 or Access 2007-2010 format, as long as you're using Access 2007 (or later).

    In Access 2003 and before, there is no option to add totals to a datasheet, you'd have to create a report for that.


    Regards, Hans Vogelaar
    • Marked as answer by Bruce Song Wednesday, November 16, 2011 7:00 AM
    Friday, November 4, 2011 2:30 PM
  • Am using an A2k App. New to Datasheets. I would like to create a totals row for some of the columns in a form datasheet. Is this possible?

    When the user filters the form datasheet on some criteria, is it also possible for the totals to be updated to show the relevant total values only.


    In Access 2000, there's no built-in way to get a totals row in a datasheet.  You could use a continuous form instead, with a set of calculated controls in the form footer section that calculate the totals using the Sum() function.  The continuous form could be formatted to look a lot like a datasheet, but wouldn't have some of the built-in datasheet features such as simple column-sizing, -ordering, and -hiding.  A continuous form is what I would recommend unless you really need the special datasheet features.

    If you absolutely need a datasheet, you could take that continuous form, with its calculated controls in the footer, switch it to datasheet view, and place as a subform on an unbound main form.  On the main form, add calculated controls below the subform that refer to the totalling controls in the subform to get their values.  That ought to work, but bear in mind that, if you hide, resize, or rearrange columns around on the subform datasheet, that won't affect the referred controls on the main form.

     

     


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    Friday, November 4, 2011 2:38 PM
  • Hi Dirk and Hans,

    Thank you for your responses. Continous forms with a text box in the footer does the job.

    However, I also tried using a union query which seems to work - the DS's recordsource is the first query in the union query. The other is a query that totals the first query and is unioned to it.

     

    This does work but I cant seem to get the total row to change when I filter the datasheet. It just shows the same total. Any Ideas?

     

    Thanks,

    Anand

    Monday, November 7, 2011 6:00 PM
  • Thank you for your responses. Continous forms with a text box in the footer does the job.

    However, I also tried using a union query which seems to work - the DS's recordsource is the first query in the union query. The other is a query that totals the first query and is unioned to it.

     

    This does work but I cant seem to get the total row to change when I filter the datasheet. It just shows the same total. Any Ideas?


    Yes:  don't do that.  <g>

    That approach won't work if you want the totals to automatically update when you filter the results.  That is because, when you use a union query, the totals row you created is part of the static, pre-filtering set of records.  Filtering that set of records won't update the totals.

    If you really have to do it via a union query as you describe, you'd have to set up the two contributing queries (whose results will be unioned) so that they refer to criteria controls on your form for filtering.  Then to filter the results you would modify those controls and then requery the form so that the union query is processed all over again.

     


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    • Marked as answer by Bruce Song Wednesday, November 16, 2011 7:00 AM
    Monday, November 7, 2011 6:25 PM