Switch() Clause within the same field, different record?:

[Robbin]

For any given faculty member, for any given year, there is a set of committee
service preferences based on a survey. Faculty members are asked to indicate
which committees they are already serving on by “A" (Already Serving). I
have an If clause that checks the ineligible to be elected where PreferenceID
= “A†for the SAME committee (record). That works great -- except that,
there are several OTHER committees that render people ineligible to serve due
to conflicts of interest. In other words, if a faculty member serves on
FSCC, he/she may not sit on any other committee. If a faculty member sits on
CAPT, he/she is ineligible to sit on CFC, etc. And there are several of
these ineligibility rules that criss-cross other committees. Further, there
is data in other fields that also might render the faculty member ineligible
for a certain committee. For example: If the TenureStatus check box = 0,
that renders the faculty member ineligible to serve on committees that are
limited to tenured professors.

SO – up until now, I have been manually checking and unchecking eligibilites
on and off, based on this large schedule of criteria, mostly because whenever
I have been able to set some of them automatically for some criteria, I can
no longer manually change it for other sets of criteria.

I hope this makes sense. A 25 page report grouped by commitee listing every
facutly preference and their eligibiities is generated out of this. And that
is then used to build our web-based ballots. I've tried pivoting this data,
no no avail. To automate this would be SO HUGE, I can’t even tell you!
Here is how the data looks:

Committee subtable (form) of Master Faculty table (form):

Year CommitteeID Committee PreferenceID Preference Ineligible
2009 1 AAPC P Prefer not to Serve 0
2009 2 CAPT W Willing to Serve 0
2009 3 CFC A Already Serving -1
2009 4 EPC W Willing to Serve 0
2009 5 FSCC W Willing to Serve 0
2009 6 Grievances P Prefer not to Serve 0
2009 7 PPBC W Willing to Serve 0
2009 8 Ombudsperson P Prefer not to Serve 0
2009 9 CAS P Prefer not to Serve 0
2009 10 FLMC P Prefer not to Serve 0
2009 11 Faculty Teller P Prefer not to Serve 0
2009 12 EMC P Prefer not to Serve 0
2009 13 Graduate Studies P Prefer not to Serve 0
2009 14 Information Services P Prefer not to Serve 0
2009 15 Parking Appeals P Prefer not to Serve 0
2009 16 Parliamentarian P Prefer not to Serve 0
2009 17 Self-Designed Majors P Prefer not to Serve 0
2009 18 Study Away Committee P Prefer not to Serve 0

 

[Robbin]

Is it just me, or does it seem like just asking the question brings a new way
of looking at the problem?

Am I right that my data is (are) in the wrong direction? When the survey
comes in, it's in an excel spreadsheet with each committee having its own
field. Then the professors are listed with their preferences in them.
Whereas, in order to aggregate the committees, I have to turn it around by
professor, and list the committees under them. If each committee had its own
field, I see how it would be easier to create a set of switches for each one.
Am I on the right track, or down a rabbit trail?

Thank you!