How can I fill a field with info from another field

[mmacygin]

Apologies if this is super simple, but I'm having a challenge figuring it out.  I have a DB created from an .xlsx.  The data is made up of 3 fields. 1) name, 2) email and 3) roles.  In the roles field there can be anywhere from 1 to 5 different roles identified, each separated by a comma. In total, there are 10 different unique roles that are identified within the table.

In my DB, I'm creating new fields to represent each of the 10 different roles, and my desired outcome is to populate the various specific role fields with the individual roles that are in the roles field now.

In the example below, the first three fields are created and have data coming in.  The 4 new fields (ROLE1 thru 4) have been created, but need to be filled in based upon the data in the ROLES field.

NAME	EMAIL	ROLES	ROLE1	ROLE2	ROLE3	ROLE4
John Doe	email address removed for privacy reasons	role1,role3	Role1		Role3
Jane Doe	email address removed for privacy reasons	role2,role4		Role2		Role4
Jim Doe	email address removed for privacy reasons	role4				Role4
John Smith	email address removed for privacy reasons	role1	Role1

Any assistance that can be given will be greatly appreciated.

Regards,

Michael Macygin

[#2]

@mmacyginYour design is wrong. You still work as if it were a spreadsheet. But you are now working on a relational database that requires specific design rules, also known as data normalization. Learn about that first before you start building a database.
To give you an idea, your model should look something like this:

[#3]

have a DB created from an .xlsx.

Can you upload your .xlsx file?

[#4]

I'll have to modify the xlsx before uploading. (personal information stuff)

[#5]

@mmacygin 

How about convert the Excel sheet layout like below before import to Access?

Then I guess use transform pivot sql.statements.

https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/transfor...

https://b23.tv/2NG26F0

//select * from split_then_transform limit 20;

cli_split_data~split_then_transform~,~ROLES;

select NAME,EMAIL,ROLES name from split_then_transformsplit;

source and expected result:

NAMEEMAILROLES

John Doe	email address removed for privacy reasons	role1,role3
Jane Doe	email address removed for privacy reasons	role2,role4
Jim Doe	email address removed for privacy reasons	role4
John Smith	email address removed for privacy reasons	role1

NAMEEMAILname

John Doe	email address removed for privacy reasons	role1
John Doe	email address removed for privacy reasons	role3
Jane Doe	email address removed for privacy reasons	role2
Jane Doe	email address removed for privacy reasons	role4
Jim Doe	email address removed for privacy reasons	role4
John Smith	email address removed for privacy reasons	role1