For Each Field in a Table...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table that has the following data (Level1, Level2, Level3 being
headers):
Level1 Level2 Level3
1/1/2003 1/1/2004 6/1/2004

Using code, I want to create a new table where the field names in the above
table become data in the new table:
levels level_date
Level1 1/1/2003
Level2 1/1/2004
Level3 6/1/2004

Can I use "For Each" to go through each field in the 1st table and add that
field name as a value in the levels field in the new table? Or is there a
better way??

Thanks,
J
 
J said:
I have a table that has the following data (Level1, Level2, Level3
being headers):
Level1 Level2 Level3
1/1/2003 1/1/2004 6/1/2004

Using code, I want to create a new table where the field names in the
above table become data in the new table:
levels level_date
Level1 1/1/2003
Level2 1/1/2004
Level3 6/1/2004

Can I use "For Each" to go through each field in the 1st table and
add that field name as a value in the levels field in the new table?
Or is there a better way??
Click to expand...

I'd use three append queries, along these lines:

INSERT INTO NewTable (IDField, LevelNo, LevelDate)
SELECT IDField, 1, Level1 FROM OldTable
WHERE Level1 Is Not Null;

INSERT INTO NewTable (IDField, LevelNo, LevelDate)
SELECT IDField, 2, Level2 FROM OldTable
WHERE Level2 Is Not Null;

INSERT INTO NewTable (IDField, LevelNo, LevelDate)
SELECT IDField, 3, Level3 FROM OldTable
WHERE Level3 Is Not Null;
 
Hi Dirk -

The problem is that I want to allow for more columns than what exists
currently. The original table is coming from another system and an upgrade
will be coming out that will have additional columns (levels) in the table,
but I currently don't know how many. I could still use the append queries,
but can I use a variable for the field name (level1 etc) and cycle through
the field names in the original table and run the append queries based on the
variable (field name)?

Thanks,
J
 
J said:
Hi Dirk -

The problem is that I want to allow for more columns than what exists
currently. The original table is coming from another system and an
upgrade will be coming out that will have additional columns (levels)
in the table, but I currently don't know how many. I could still use
the append queries, but can I use a variable for the field name
(level1 etc) and cycle through the field names in the original table
and run the append queries based on the variable (field name)?
Click to expand...

I suppose you could do something like this (air code):

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strSQL As String
Dim strFieldName As String
Dim strLevelNo As String

Set db = CurrentDb
Set tdf = db.TableDefs("ImportedTable")

For Each fld in tdf.Fields

strFieldName = fld.Name

If strFieldName Like "Level*" Then

strLevelNo = Mid$(strFieldName, 6)

strSQL = _
"INSERT INTO NewTable (IDField, LevelNo, LevelDate) " &
_
"SELECT IDField, " & strLevelNo & ", " & strFieldName "
& _
"FROM ImportedTable WHERE " & strFieldName " & "Is Not
Null;

db.Execute strSQL, dbFailOnError

End If

Next fld

Set tdf = Nothing
Set db = Nothing
 
Back
Top