SUM of 206 fields

[Scoops]

I have a table with 207 fields, the primary being a word the rest numbers. How do I return the SUM of the last 206 fields without running into 1026 letter limit in sql string. THANKS IN ADVANCE

 

[chergh]

Restructure you database so it's not an excel spreadsheet in access would be the best way.

 

[Scoops]

I can't restructure, very large database. If it can't be done, I will have to write vba macro

 

[Ginny2222]

Would this work:
In your query add
1: Name-of-Field 2: Name-of-Field ... 206: Name-of-Field,
to customize the names for each field. Then your Aggregate function can be
Expr1: 1+2+3+4+5 ... +206.

No limits passed with this.
Ginny

 

[Scoops]

I don't understand. could you please explain. THANKS

 

[RuralGuy]

Chergh gave some pretty good advice. I suspect you will continue to struggle with this table until it is normalized.

 

[Ginny2222]

If you can’t normalize (are you sure?).
When you build your query and add each field to the grid, you can then add new names in front of the field names from the table in the format:
New_name: Old_name
You can then use the ‘new_name’ as a variable in your Expressions. Using 1, 2, 3, 4, etc. as the ‘new_names’ for each of the fields you are minimising the string in your expression. Does this explain it?
Ginny

 

[Scoops]

I am not including in query the 206 just the result

 

[Ginny2222]

That's ok, you just untick the 'show' for all fields except the Field with the Expression.
Ginny