Question Consolidating multiple rows into one?

[Mr Smin]

Can anyone tell me how to get from this

Table1:
T1_ID Notes
1 note1
2 note2

to this

Table2:
T2_ID Notes
1 note1 note2

?
I am trying to make notes from an old database available in a new one which has a different structure.

 

[DavidAtWork]

Mr S Are you saying:
T1_ID Notes has fields named: 1 Note1 and 2 Note2 and you want to concatenate them into one field

Or
T1_ID Notes links to another table of 'Notes' and for each ID in T1_ID Notes you want to concatenate all records in 'Notes' in into one record

David

 

[Mr Smin]

Hi David,

I am trying to do the second option you gave. Sorry, I should have labelled my explanation more clearly.

 

[DavidAtWork]

To do this efficiently you need to do this in VBA and open 2 recordsets, an outer loop of all the records in table T1_ID Notes
take each T1_ID Notes id in turn and using the inner loop query all records from the second table 'Notes' where id's are same and then concatenate the values from the notes field and when you reach the EOF for the inner loop use some sql to update an existing OR append a new record to T2_ID Notes.
Move to next record in outer loop etc

David