Restructuring Data 1

[carlosfandango]

I have a number of tables each with the same 10 unique ID's in. In each table there are numerous figures about each record, the fields have the naming convention "Code _ Timeframe" for example SSX32_0304 for the item of data SSX in 2003/04.

The problem is I need to append all the data in all the columns into one. So I have a huge list containing the unique ID then the column name then the data item.

Is this at all possible easily as opposed to listing all of the fields in a union query as there is hundreds?

Cheers
Carl

 

[TomCologne]

Look into the "UNION Operation" in VB Help. If that doesn't work for you, I'd probably try something like this:

Function ListFields(ByVal strTabName As String) As String

Dim dbs As DAO.Database
Dim TDef As DAO.TableDef
Dim MyField As DAO.Field
Dim strList As String

Set dbs = CurrentDb
Set TDef = dbs.TableDefs(strTabName)

For Each MyField In TDef.Fields
    strList = strList & vbCrLf & MyField.Name
Next MyField


ListFields = strList

End Function

to get at least the field names.


TomCologne