Now that I have your attention here's my predicament - I'd like to translate the output of a query which reports the "training level completion" for a group of technicians (Larry, Curly, and Moe) into a more compact format. That format is essentially a nested IF statement, and easy enough to implement if not for the 4 different skill levels. The idea is to report the highest skill level with 100% completion.
In simple words,
- For each Technician (in rows), and for each Area (in columns) -
- Starting with Advanced skill level, is the value =100%? If so, then capture the value of “Adv” for the report
- Else, move to next lower skill level, Moderate, and check is value=100%? If so, then capture the value of “Mod” for the report
- Else, move to next lower skill level, Basic, and check is value=100%? If so, then capture the value of “Bas” for the report
- Else, move to next lower skill level, Trainee, and check is value=100%? If so, then capture the value of “Trn” for the report
- Else, capture a blank for the report
Here's what the sample Table looks like, and what the desired Report would look like (with some cond formatting) which was simple to do in Excel.
Thanks in advance!