How to sum total values in multiple column?

[mabrynda]

Hi,
Is there any possibility to sum all the values in many (say n) columns in a single table using VBA in access 2003?
Say I have a table with 200 columns. Say the table name is TableCOUNT and the columns inside the table have names such as LX1, LX2,....,LX200.
If I use DSum for a single (or a few) columns giving the name of the column, everything is OK. I get a total for this (these) ciolumns:

Open "N:\FM AM\TEST_LOG1.txt" For Output As #intOutFile
Print #intOutFile, "Printing sum of a single column LX5 in TableCOUNT table"
 
       curX1 = DSum("[LX5]", "TableCOUNT")
 
Print.Debug curX1
Print #intOutFile, curX1
Close intOutFile
 
 

But if I want to go through 200 columns with a loop, the DSum instead of returning the sum in each column, returns the sum of the NUMBER OF CELLS IN EACH COLUMN!!! (eg. If I have 200 columns with 10 cells I get a sum of 2000, with intermediate values in the LOOP being 10, 20, 30,...,2000:

 
Open "N:\FM AM\TEST_LOG1.txt" For Output As #intOutFile
Print #intOutFile, "Printing sum of each column in TableCOUNT table"
 
For yy = 1 To 200
 
       curX1 = DSum(yy, "TableCOUNT")
       If curX1dif < 1 Then Print #intOutFile, "WARNING!!! All values in this Fund are ZERO"
 
Print.Debug curX1
Print #intOutFile, curX1
Next yy
Close intOutFile
 

Any ideas to solve this problem?

Thanks in advance

[nico5038]

This DSUM() approach will be slooow.
I would start with creating a GroupBy (Use the "E" or "Sigma" button to activate) and place all fields. Next add Sum() as the aggregation function.
Now all 10 rows will be totalled and when you want a loop you can loop through the individual (SumOf...) fields.
Faster is however to create a new query based on this groupby query and add all fields like:

SELECT L1 + L2, + L3 +.... FROM qryGroupBy

Now a simple single DLOOKUP can be used to write the result to your file or even better a Docmd.Transfertext can be used to create a textfile from the query result.

Idea ?

Nic;o)

[MMcCarthy]

Please use code tags when posting code

Check out How to ask a question

[MMcCarthy]

Nico is right about the DSUM approach and I would advise following his strategy. However, if for some reason you need to keep the DSUM approach then you can use a FOR ... LOOP to output your data as follows:

Dim i as Integer
 
    For i = 1 To 200
        curX1 = DSum("[LX" & i & "]", "TableCOUNT")
        If curX1dif < 1 Then Print #intOutFile, "WARNING!!! All values in this Fund are ZERO"
 
        Print.Debug curX1
        Print #intOutFile, curX1
    Next i
 

[mabrynda]

Thank you very much for quick response. I didn't try Nico5038's strategy yet, but simple loop proposed by msquared works great!
Thanks again. That solved my big issue.

[MMcCarthy]

I'm glad you got it working. I would take the time to explore Nico's suggestions though as his approaches would be a lot more efficient. Even if you don't use them this time there is always the next :D