Converting column data to rows (1 Viewer)

[RedSkies]

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:

AgentID Campaign1 Campaign2 Campaign3...
12345     Yes           No                Yes
33442      No           No                Yes

What I want it to look like is this:

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]

I'd use a UNION ALL query, but what do I know.

 

[RedSkies]

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.

 

[pbaldy]

But now I need to figure out how to step through each of the column names in the imported table

How about:

  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