I have a table with one record per certification a user has. So, user SMITH can have multiple records, one for DMV another for CPT and another for ICD. SMITH has three certificates but can have up to 20.
The desire is to have a second table with one record per user with columns for each certification indicating with Y or N whether the user has that certificate. The column names are the same as the certificate name (eg. DMV, CPT, ICD,...)
So, is there a way to read in the first table, use the data in the certificate field (DMV, CPT, ICD, etc) to then reference the column in the second table and update the respective column to a "Y" to indicate the user has that certificate? For example: if table2:ColumnName(DMV)=table1:Certificate("DMV") then update table2:Column(DMV)="Y"
The input file we use to create table 1 is in the one record per certificate per user design. We do not control that.
The intent is to avoid long Case statements. If this works, we have another similar application with a possible 130 roles a user can have. I've Googled this multiple ways and read lots of possibilities but none seem to indicate this can be done or I just don't understand it.
Can I do this? If so, how? Any help will be much appreciated. Thank you!