Sub Summary Report sorted by related fields.
Okay here is the dilemna:
I have a table called Staffers in which I have the following fields: Staff Full Name, Activity #1, Activity #1B, Activity #2, Activity #2B, Activity #3, Activity #1a Notes, Activity #1b Notes, Activity #2a Notes, Activity #2b Notes, Activity #3 Notes.
So in short the staffers table has a list of staff as well as the activities they are able to do with notes for each activity.
What I need is a report similiar to a Sub Summary report that will list all Staffers who can do a particular activity. The issue is that since the activities are listed in order or preference (ie. 1a is more important than 2b) for another report used. So let's say I have the following data
Staff Full Name Activity #1 Activity #1a Notes Activity #1B Activity #1b Notes
John Smith Swimming Strong Archery New to this
Jane Cole Sailing Captain Swimming Instructor
Bob French Archery Instructor Sailing Student
As you can see some staffers may have the activity listed in any of the activity fields. I need the report to group ALL staff who do a particular activity.
John Smith - Strong
Jane Cole - Instructor
John Smith - New to this
Bob French - Instructor
Jane Cole - Captain
Bob French - Student
What can I do?