Choose which columns to sum in a Total Row (1 Viewer)

[George21]

Hi. I have a query that I need to get the sum on certain columns.

As a first step I added the following code in order to create the Total Row at the bottom.

CurrentDb.QueryDefs("qryPayroll").Properties("TotalsRow") = True

Since the query's columns (fields) change every time because its source table is dynamic, I cannot determine which columns to sum up and save the query. I need to add the Sums in the specific columns through programming.

Any suggestions?

Thanks.

 

[Ranman256]

why is it dynamic? The fields should not change. A table only has X fields.
Seems to my you only need sum the Amt column. (not the Name column)
simply sum that column, or sum all the numeric columns and pick the one you want.

 

[George21]

It is dynamic because the fields are been created by another table and depending on this table's data.

For example, Company A's salaries components are made of: Basic, Overtime, Leave Pay
Company B's salaries components are made of: Basic, Overtime, Bonus

So, now I want to make a table for a specific company that looks like a crosstab (but not a crosstab query as I want to be able to manipulate the data inside).

The rows will consist of the records (being each employee) and the columns will be every salary component.

Example:

EMPLOYEE BASIC O/T LEAVE TOTAL
Nick - - - - - 100 - - - - 50 - - - - 10 - - - - 160
George - - - 160 - - - - 80 - - - - 15 - - - - 245

TOTAL: - - - THIS IS WHERE I WANT TO CHOOSE WHICH COLUMNS TO ADD

 

[Cronk]

Like Ranman, I too can't see why the fields in your query would change unless you are changing the sql of your query. But then again your query could be
select * from YourTable

Anyway, having set the TotalsRow property to true, you could try looping through the query's fields to test the field type and then for those you want to sum, set that field's aggregateType property to whatever.

Something like (not tested)

for n = 0 to qdf.fields.count -1
  if qdf.fields.properties("Type") = dbDouble  'add whatever other field types
     qdf.fields.properties("AggregateType")=3
 endif
next n

 

[George21]

Thanks Cronk.

"Aggregate Type" is what I was looking for at last! (BTW is there a site where I can see all the properties I can input through VBA)?

To answer your and Ranman's question, it's true that my query is select * so I have to go through it and check some criteria before summing the field. May be it was not necessary to point that its source is a dynamic table...

 

[Cronk]

I don't know of any site but you can enumerate by looping through the properties, something like (not tested)

for n = 0 to qdf.fields("yourfieldName").properties.count -1
     debug.print qdf.fields("yourfieldName").properties(n).name
next

 

[George21]

Thanks! This is helpful.