How to split colum value (with comma) to multiple columns

[Asita]

Hello Good Morning,

Could you please tell me how to split this , I am new Access DB, I worked in Sql Server but access new to me please help me here

I have the below data
Appid Names
100 John,Bob,Kondya, Lima
200 Shor,James,Kim
300 Cinem

I want to convert to

Appid Names Name1 Name2 Name3 Name4
100 John Bob Kondya Lima NULL
200 Shor James Kim NULL NULL
300 Cinem NULL NULL NULL Null

Thank you in Advance
Asita

 

[plog]

That's not how it should be done. Tables should grow vertically (with more rows) and not horizontally (with more fields). So you should look to put those values into numbered fields, but into new records. This record:

Appid; Names
100; John,Bob,Kondya, Lima

Should become these records:

Appid; Name
100; John
100; Bob
100; Kondya;
100; Lima

To achieve that you would most likely use VBA to read in each existing record, split out all the individual names, then write an INSERT statement for each name into the properly structured table that I laid out.

 

[Asita]

Good Morning ,

Thank you for your reply,

this is just a copy data from access to excel for a report, could you please help me with sql query,

is there any possibility? because my firm is very strict to not use vb

Please

Thanks Again
Asita