I have built a dynamic crosstab query where the user selects options from multiple combo boxes and then I use VBA to update the SQL for the cross tab query. It is then, in turn, displayed on a subform. My question is basically how can I programmatically (VBA or SQL, don't really care either way) add a totals row that calculates the average of each column. I know how to do this manually, but since the number of columns and column headings are not set, this is impractical. I would basically like to automatically set a totals row at the bottom for the average of each column regardless of the number of columns or column headers. An example of a potential case is shown below with the user inputs on top.
Group: A
Team: All
Start Date: 1/1/2014
End Date: 5/31/2014
Query Type: Monthly
Name 2014-Jan 2014-Feb 2014-Mar 2014-Apr 2014-May
John Doe1 1 2 3 4 5
John Doe2 2 2 2 2 2
John Doe3 1 5 9 13 17
John Doe4 2 10 18 26 34
John Doe5 1 8 15 22 29
John Doe6 2 12 22 32 42
John Doe7 1 15 29 43 57
John Doe8 2 14 26 38 50
John Doe9 1 12 23 34 45
Total (Avg): 1.44 8.89 16.33 23.78 31.22
I am trying the generate the bottom "Totals" row programmatically without knowing the column count ahead of time and without manually inserting totals via the ribbon.
