Using queries to create new table

[mradel]

I have a table as follows:



Name: Addr: City: State:
1 Mike 123 Pine Mpls MN
2 John 25 Jones LA CA
3 Bill 9 North Dover IA

Is there a way to change the table as follows?

Name: Data:
1 Mike 123 Pine
1 Mike Mpls
1 Mike MN
2 John 25 Pine
2 John LA
2 John CA
3 Bill 9 North
3 Bill Dover
3 Bill IA

Is there an easy way to complete this type of conversion? I have a table that has 12 feilds and 806 records that I need to convert in the the new table format.

Thanks for the help.

[ajetrumpet]

try making a new table with 2 fields in it, as you stated above. then loop your old table...

for instance, something like:

dim rs as recordset
set rs=currentdb.openrecordset("table")

dim i as long
dim sname as string

with rs
   .movelast
   .movefirst
      do until .eof
         for i=0 to rs.fields.count-1
            if not rs.fields(i).name="name" then
               currentdb.execute "INSERT INTO newtable ( name, data ) " & _
                                 "VALUES ( '" & sname & "', '" & rs.fields(i) & "' )"
            else
               sname = rs!name
            end if
         next i
                  .movenext
      loop
end with

rs.close

set rs=nothing

using that many append query actions at the speed that vba code executes may be problematic for large data sets, but it's obviously worth a try. backup your file before trying this, if you do. with only 800 records to do, I doubt it'll cause an issue.