Append from flat file to a new file.

[Tom]

OK, here is the problem. I have a file that contains over 200,000
records, primarily because each field is imported as a separate
record. For example, there are 1,309 fields for each company and each
field is imported as a separate record with five fields as follows:

Rec # -------------------Field # / Name-----------------------------
1 2 3 4 5
Control # Data Field No Field Name Field Desc

1 000001 A 1 Data1 1st Data Element
2 000001 B 2 Data2 2nd Data Element
3 000001 C 3 Data3 3rd Data Element
4 000001 D 4 Data4 4th Data Element
......

1,309 000001 999 1,309 Data1309 Last Data
Element


I want to create a separate table(s) that will have a separate record
for each control number as follows:

Rec # -------------------Field # / Name-----------------------------
1 2 3 4 5
Control # Data1 Data2 Data3 Data4

1 000001 A B C D
2 000002 A B C D
3 000003 A B C D
4 000004 A B C D

Any suggestions you have would be greatly appreciated. Obviously I am
a novice at this.

Thanks in advance for any guidance you can give me.

Please forward a copy of your response to: (e-mail address removed)

 

[John Vinson]

1 2 3 4 5
Control # Data1 Data2 Data3 Data4

Note that Access allows an (absurdly large, IMHO) limit of 255 fields
in a table. If you want 1309 fields across, you will NOT be able to
create such a table in Access - and you shouldn't!

The table structure that you now have is correctly normalized. Could
you explain why you feel you need to turn it 'sideways'?

John W. Vinson[MVP]
(no longer chatting for now)