Combine records (1 Viewer)

[sloaner14]

I need to know how to combine two records. What I have is a text file that is imported into a table. The problem is, the text file has 4 fields on one line then 3 fields on the next line. Is there a way to combine these two lines into one record? I do not want to do this in the text file. I want to import the file and run some code to combine the two records into one record, delete the second line, and go to the next two records. What can I do? Sorry for any spelling but I have to run. Thanks for any help.

 

[neileg]

If you run a query on the table and test the fourth field for null, this will identify the lines that have three fields. Obviously if the field is not null this is is a four field line. You can them combine these two queries into one query that assembles the 7 fields. Of course, you will have a problem in that you don't have a field to join the two queries!

So, if you import your text file into a table with an autonumber, your consecutive lines will be one number apart. Use a calculated field in one of your queries that adds or subtracts one from the autonumber and that will give you the join field.

Somebody else would be able to come up with some VBA to do this on import, but I'm not that clever so I stick to SQL.

Thinking about it again, if your fourth field is sometimes null anyway, you could use the odd and even autonumbers to split the file into the two queries.