Adding Totals to a query using queryDef

[jrickels]

In Access 2007 we are able to add totals to the bottom of queries, I was wondering if it is possible to add totals to a query in vba using a queryDef?

[llkhoutx]

Sure, just use a group query on your query, but only on field to be summed.

[jrickels]

Maybe you are misunderstaning my problem (or I could be misunderstanding your solution) . Here's the whole situation, keep in mind I know there are other ways around this and in the future I plan on changing the setup of it, but until then I was hoping for an easy solution to get me by.

I have a query that I create using a query def in vba code (because I change the source of the query several times). After I create the query, I can go in and manually add the totals by clicking the Totals button on the Home Ribbon and defining that I want a sum in each of the numeric columns. When I look at the SQL of this query after making this change it did not change from the original version set up using the query def. There for I decided it was a property of the query, so I was wondering if there was a way to change/add that property to a query using vba code?

[llkhoutx]

You don't say that you save the query after you've summed appropriate columns.

You can programmatically determine which columns are numeric and then programmatically build a SQL string which sums those columns.

[c_smithwick]

For example:

SELECT Sum(tblTest.Field1) AS SumOfField1, Avg(tblTest.Field2) AS AvgOfField2, Count(tblTest.Field3) AS CountOfField3 FROM tblTest

[jrickels]

yes, I save the query after I've summed up the columns

I need all of the detail and the totals in one query. I know how to get the total of each of the columns, but that is not what I'm looking for. I am wondering if anyone knows how to change/update the property of 'Totals' on a query if it is possible

[c_smithwick]

SELECT TblData.[Field1], TblData.[Field2], TblData.[Field3]  FROM TblData
UNION SELECT Sum(TblData.[Field1]) AS [Field1], Sum(TblData.[Field2]) AS [Field2], Sum(TblData.[Field3]) AS [Field3] FROM TblData;

Last row of the query result will be the totals. All fields must be named the same as the select query for it to work however

[jrickels]

That's creative, thank you. I will probably end up using that.

I'm taking the lack of response as no one knows how to add the 'Totals' property to a query,if it is possible.