I have a table (compiled tracker) used to track certification attainment within my organization. I have a few columns that are used to show compliance with my org's requirements in regards to attaining certificates (required certificate category and certificate obtained). I added a column (compliance) that would calculate if a member's certificate obtained matches the required certificate for their position. The member can get any certificate in a specific required category and be compliant. Some certificates are present in multiple categories. I have created tables with each of the categories and their associated certificates
I need help creating an IF( statement that will allow me to determine if a member's obtained cert meets the required cert category of their position.
The main table,"Compiled Tracker", with several columns including: "Cert Compliant", "Cert Type", and "Cert Level Required"
Cert Level required is related to 3 tables: [name in " ", data below]
"IAM I"
CAP
GSLC
Security +CE
"IAM II"
CAP
CASP CE
CISM
CISSP
GSLC
"IAT II"
CCNA
GICSP
GSEC
Security+ CE
SSCP
Cert Level Required has locked values and must be either IAM I, IAM II, or IAT II
I need help creating an IF( statement that will allow me to determine if a member's obtained cert meets the required cert category of their position. A member can have any cert in a specific category and be compliant, IE: If(cert level required="IAM I" and Cert Type=[Any in IAM I] then "Compliant", "Non compliant".
The data in compiled tracker has columns like name, organization, cert expiration date and the others i mentioned earlier (Cert Compliant, Cert Type, and Cert Level Required. Everything except cert expiration is a string.
I created a relationship between the cert type column of compiled tracker table and the only column in IAM I, IAM II, IAT II respectfully.
I've tried RELATEDTABLE, RELATED, LOOKUP to no avail. Maybe my syntax is wrong but I either get several errors or I won't get the response I should.
Aucun commentaire:
Enregistrer un commentaire