How to sum up special column (1 Viewer)

[Ben_Entrew]

Hi all,
is it possible to tell VBA to sum up all columns with the column name containing a special string.

For example I want to sum up all columns containg 2013 as a column name.


Thanks in advance.

Regards,
Ben

 

[pr2-eugin]

Quiet a bit of coding would be required. Are you sure you cannot do something with Query design? Selecting only Dates restricted to the year you want?

 

[plog]

Pertinent information shouldn't be stored in table or field names. It sounds like your data is improperly structured. If 2013 is relevant to the data, it shouldn't be in your field name, but in a field of its own (i.e. ReportYear).

Then to sum up everything in 2013, you enter 2013 as the criteria and run an aggregate query summing up the appropriate fields.

Can you post your structure?

 

[Ben_Entrew]

Hello Plog,

the issue is that my source data with the actual numbers is in the format like :

Reporting_Month, Customer,Quantity
012013,aaa,552
022013,bb,222
..
082013,cc,523

Now I get a table provided containing the forecast numbers:
Customer, 092013,102013,112013,122013
aaa,3424,43242,452342,23123
bb,123213,123412,1234,643532

Unfortunately I can't change the format, it's coming from another team here.

I get the TNS table with a cross tab query in the structure of the forecast table
and then I match it via the customer id.



Regards,
Ben