Access Datasheet Column Totals

Office for Mere Mortals helps people around the world get more from Word, Excel, PowerPoint and Outlook. Delivered once a week. free.

If you have a datasheet subform on an Access form, containing numeric data in some columns, you may want to have column totals at the bottom of the datasheet. You can do this easily in an Excel workbook or a Word table, using a formula, but Access does it differently. In Access 2007, column totals were introduced. Apparently, there have been some enhancements in this feature since that time, as the Help article says that this feature is only supported in the datasheet portion of split forms. In Access 2010, however, it works just fine in a regular datasheet subform.

As an example, here is how to add column totals to the datasheet subform on the time sheet form from the Advanced Time Sheet database (Access Archon #242). First select or enter an employee and some work hours (so you will have something to total), then open the datasheet subform in datasheet view, and click the å Totals item in the Home group of the Ribbon. A new Totals row appears. For each column that you want to total, click on the left side of the Totals row until the drop-down shown below appears, and select Sum (or whichever function you want). Repeat for each column until they all have totals.

AW 1708-D

If the totals don’t appear at once, click F9 to requery the datasheet.

Want More?

Office Watch has the latest news and tips about Microsoft Office. Independent since 1996. Delivered once a week.