My table tbl_Courses contains data on all the 200+ courses we offer to workers at an industrial site. The workers are required to complete XX clock hours in the learning lab before they undergo a supervisor’s evaluation every two years. Supervisors evaluate employees using nine performance criteria, and most of our courses support issues underlying one or more of those nine criteria. Some courses support a single criterion; others support two or even three criteria. Courses that do not support a criterion we list as electives.
My task is to produce a printable document–an MS-Access report–that will display all our courses, but sorted by the criterion number. This would be easy if all courses supported just one criterion. However, some courses will appear two or three times in this report. For example, course number 126 will appear under Criteria 2, 5, and 7. I’ve tried two approaches to designating the appropriate criterion in the table. In the first approach, I draw on the fact that no course supports more than three criteria. I have three fields–Crit_A, Crit_B, Crit_C–and I simply keyed in the appropriate numbers for each course. If a course supports but one criterion, that criterion number goes under Crit_A, and the other two fields remain empty.
The second approach involves 10 fields: one for each criterion and one for electives. I labeled the fields Crit_1 through Crit_0 (zero for electives), and formatted the fields as Yes/No.
What I haven’t worked out is how to set up a query to sort out this mess and produce the report I want. My attempts have resulted in either a blank query or a query that displays each course 10 times (once for each criterion plus elective)! I’m still kinda new to this stuff, and I need some help. Anybody…? TIA
