How can I fill a field with info from another field

Question

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

xps35 · Answer

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:

peiyezhu · Answer

have a DB created from an .xlsx.Can you upload your .xlsx file?

mmacygin · Answer

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

peiyezhu · Answer

mmacygin&nbsp;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/transform-statement-microsoft-access-sql&nbsp;https://b23.tv/2NG26F0&nbsp;//select * from split_then_transform limit 20;cli_split_data~split_then_transform~,~ROLES;&nbsp;select NAME,EMAIL,ROLES name from split_then_transformsplit;&nbsp;source and expected result:NAMEEMAILROLESJohn Doeemail address removed for privacy reasonsrole1,role3Jane Doeemail address removed for privacy reasonsrole2,role4Jim Doeemail address removed for privacy reasonsrole4John Smithemail address removed for privacy reasonsrole1NAMEEMAILnameJohn Doeemail address removed for privacy reasonsrole1John Doeemail address removed for privacy reasonsrole3Jane Doeemail address removed for privacy reasonsrole2Jane Doeemail address removed for privacy reasonsrole4Jim Doeemail address removed for privacy reasonsrole4John Smithemail address removed for privacy reasonsrole1

Forum Discussion

How can I fill a field with info from another field

Share

Resources