By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,462 Members | 2,308 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,462 IT Pros & Developers. It's quick & easy.

Sum of values in multiple columns - Column names keep changing every week

P: 11
Hello,

I have a problem with adding values...
If the table headers are unchanged then adding values from different columns is not a problem. But in my table, the headers keep changing every week.

Could someone please help me with the VBA code to add columns irrespective to the column names? One option I have is the header names start with OH 2300, OH 2301 ...

Is there anything like: DSum anything starts with OH or Like"OH *" ?
Jun 12 '12 #1
Share this Question
Share on Google+
3 Replies


Rabbit
Expert Mod 10K+
P: 12,315
Your data structure is unnormalized. You need to normalize it so you don't have changing field names. That way you don't have so much trouble querying.
Jun 12 '12 #2

P: 11
Hello Rabbit,

Thanks much for the reply.
I accept that the data structure is not normalized and there is no way for me to change it now.

Please let me know if there is any way to make it possible with the present data structure.
Jun 13 '12 #3

zmbd
Expert Mod 5K+
P: 5,285
SriKumar
I can understand your pain about being "locked" into a poorly designed database; however, every momement that you spend to get that db into a normalized form will pay you back at least a hundred fold!

In the mean time please start with the following:

http://support.microsoft.com/kb/210362 Yes, it's for MSA2000; however, it still applies.

and
Expand|Select|Wrap|Line Numbers
  1. Sub FieldNames()
  2.     Dim zrs As Recordset
  3.     Dim zfld As Field
  4.     '
  5.     'establish the recordset
  6.     Set zrs = CurrentDb.OpenRecordset("YourTableNameHere")
  7.     '
  8.     'step thru the field names in the open table
  9.     For Each zfld In zrs.Fields
  10.         MsgBox (zfld.Name)
  11.     Next
  12.     '
  13.     'cleanup
  14.     zrs.Close
  15.     Set zrs = Nothing
  16. End Sub
DON'T ASK ME WHY I HAVE THAT JUST SITTING AROUND... yep... I had a DB that was not normalized... not anymore! Over 5 years of data in that monster and the tables were monsters! It's "fixed" now and I don't regret the time I spent on it at all!

-z
Jun 13 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.