Move Data to Next Column Over

[Jerseynjphillypa]

Is there an easier way to transfer the data table to the next column over in the Final Table.

So if there is data under Jan2012 in the data table, it should be moved to the Final table in Feb2012 field by Item .

[June7]

Since table is empty, you could run an INSERT SELECT sql action.
INSERT INTO [Final Table](Item, [Feb 2012])
SELECT Item, [Jan 2012]
FROM [Data Table];

If the FinalTable already has records, run an UPDATE sql action.

This table is not normalized structure. How many years will this table need to handle?

[Jerseynjphillypa]

I would like for it to continue until the last column that appears in the table

So the last month that should appear in the Final Table is July 2012.

But the field name will change the next time I update the table. So is there a faster way without listing the names in the SQL

[June7]

You won't have data into 2013, 2014, 2015, etc?

Options:

1. Manually edit the Access query object.

2. Use VBA to construct and execute the SQL action. The code would reference controls on a form where you would input the field names.

[Jerseynjphillypa]

Yes I will have data going into 2013, 2014, 2015, etc?

If I do it with Option 2, Could you show me an example how to do it with controls

[June7]

Data is not in a normalized structure. Only 255 fields allowed in a table. That will allow 21.5 years. Probably enough for your needs.

Code in some event (perhaps button click) behind a form would be like:

CurrentDb.Execute "INSERT INTO [Final Table](Item, [" & Me.textboxname1 & "]) SELECT Item, [" & Me.textboxname2 & "] FROM [Data Table];"


If you need better understanding of programming in Access, review http://office.microsoft.com/en-us/ac...010341717.aspx