Converting column data to rows (1 Viewer)

RedSkies

Registered User
Joined
Aug 17, 2000
Messages
48
I'm sure this has been discussed before but I can't seem to find any references to it.

I have data imported from an Excel spreadsheet that contains data on the campaigns an agent is enrolled in and looks essentially like this:

Code:
AgentID Campaign1 Campaign2 Campaign3...
12345     Yes           No                Yes
33442      No           No                Yes
What I want it to look like is this:

Code:
AgentID     EnrolledCpgns
12345        Campaign1
12345        Campaign3
33442        Campaign3
I know I need to use a recordset with .AddNew along with an IF statement to check whether the value in each of the Campaign# column is Yes or No (write a new row using the column name if it's Yes, but not if No, etc.) but I just can't think how to get going with this, muchless the appropriate syntax. It's been a while since I've done any serious VBA coding.

Any tips on getting started would be greatly appreciated.

Thanks, all!
 

FoFa

Registered User
Joined
Jan 29, 2003
Messages
3,672
I'd use a UNION ALL query, but what do I know.
 

RedSkies

Registered User
Joined
Aug 17, 2000
Messages
48
Thanks Fofa, that's an approach I hadn't considered. But I still have the issue of the columns being dynamic. I never know how many there will be or what they'll be named when I import the spreadsheet.

So I guess that means I'll still have to build the SQL on the fly. But now I need to figure out how to step through each of the column names in the imported table (each one after the AgentID column, that is).

Is that a "Columns Collection" thing?

Thanks again.
 
Last edited:

pbaldy

Wino Moderator
Staff member
Joined
Aug 30, 2003
Messages
33,651
RedSkies said:
But now I need to figure out how to step through each of the column names in the imported table
How about:
Code:
  Dim db               As DAO.Database
  Dim tdf              As DAO.TableDef
  Dim x                As Integer

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

  For x = 0 To tdf.Fields.Count - 1
    Debug.Print tdf.Fields(x).Name
  Next x

  Set tdf = Nothing
  Set db = Nothing
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom