Create one record for every field in another table

[sailngsue]

I have a table that has one record in it hundreds of fields. I need each field to be it's own record in a different table. So easy to do in excel, but I'd like to do it with Access. Can any one give me a solution?

Thanks

[TheShabz]

Why would you want to do that?

[sailngsue]

Because I need to analyze the data, I only need some of the information but I need it to be in separate records so that I can then match it up to other information.

[TheShabz]

There isn't anything you can do with a 1 field table that you cant do with a 100000000000000000 field table. Can you describe the analysis that needs to be done?

[sailngsue]

I need to search for fields that contain clp**** and then separate that field into multiple fields ( I have already written that part of it). The other way around this would be able to import the text into multiple records.

I have a text file that is one record and uses a ~ as a delimiter between fields.
I used the DoCmd.transferText Statement
DoCmd.TransferText acImportFixed = 1, , "NewImport", "W:\NewImport.txt
It imported as one record creating field names field1....field255.
What I really want it to import is one field with as many records as it needs.

any suggestions?

[TheShabz]

SO what you have is:

Word1~Word2~Word3~Word4~Word5~

and you want:
Word1
Word2
Word3
Word4
Word5

then to find all the clp*s. Correct?

[sailngsue]

correct that is what I need