How to combine records in a table

I am trying to repair the (ugly) data I have had to import to my database. I am working with Access 2000. The problem is this: For many of the records, the data I am receiving (and have no control over) is split into multiple entries because one of the fields was too large to fit into one record at the source because of size limitations on one of the fields (Info ).

The table looks like this:

RecordID (this is the autonumber primary key generated by access)
Lastname
Firstname
StudentID (unique six-digit identifier for each student)
LineNum (the order of the Info entry for this student)
Info

The Info field is the troublemaker. Most students have one or two entries to accommodate the length of the Info data for that student. Average is about three lines. Max is 22 lines! So most students have multiple records which I simply need to combine into one record. They need to be combined in the right order (by LineNum ) so that the Info data will look right.

I am not sure how to set up a query (or if there is a better method) to accomplish this.

I think it is easier to do it with VBA code.

I have attached a database as illustration. The database contains:-
a table [tblImported],
an empty table [tblStudent],
and a form.

When the command button on the form is clicked, the records in [tblImported] will be combined and appended to [tblStudent].


The code is in the On Click event of the command button. It assumes StudentID is a text field. However if it is in fact a numeric field, the single-quotes in the line:-

must be removed, that is the line must be changed to:-


Hope it helps.

Jon K said:

I think it is easier to do it with VBA code.

...

I have attached a database as illustration.

...

Hope it helps.

Click to expand...

It worked PERFECTLY. Thank you.