Many records to one

[timothyl]

Hello, I have a crystal report which I imported into Access, the table is like so
ID Insurance Name

ID1 Ins1 Name1
ID1 Ins2 Name1
ID1 Ins3 Name1
ID2 Ins1 Name2
ID3 Ins1 Name3
ID3 Ins2 Name3
ID3 Ins3 Name3
ID3 Ins4 Name3
ect

But I need
ID Insurance1 Insurance2 insurance3 insurance4 Name

ID1 Ins1 Ins2 Ins3 Name1
ID2 Ins1 Ins2 Name2
ID3 Ins1 Ins2 Ins3 Ins4 Name3
ect
where ins1 ins2 ect can all be different for different names.
Any ideas ?, there are about 20,000 records, if I can get it in to the desired structure there will be about 8,000 records. Thanks for any help you can offer

 

[ajetrumpet]

you need to get the id with the most records in the table first. then you need to create the nubmer of table fields you need based on that insurance count that you got. after that, simply loop the records and populate the fields. i am not giving a code exampel this time, cuz i don't have time, but if you want to upload a db someone could probably write a procedure for you...

 

[timothyl]

Thanks Adam, I will play with these Ideas.