H
HeatherG
Hi
I am looking to create a small peer review database that will provide
a random selection of 3 people each semester (6 month period) who will
provide a peer review for each staff member. We currentIy have 70
staff. I have created the following tables"
tblStaff - IDStaff (Autonumber) FirstName, LastName, EMail
tblFrequentInteraction - IDInteractionF (Autonumber), IDStaff (Long),
IDFrequent (Long), IDCycle (long) which stores the staff ID's and the
ID's of those people with whom they frequently interact (minimum of 6
people per staff, per cycle, but can be more)
tblReviewFrequent - IDReview (Autonumber), IDStaff(long)
IDFrequent(Long), IDCyle(long) to store the selection for peer review.
I can create the append query to randomly select 3 staff from the
tblFrequentInteraction to review each staff, but we would like to
further restrict this selection so that each staff member doesn't
review more than 3 people per cycle.
My question is how do I prevent staff from being randomly selected,
once they appear 3 times in the tblReviewFrequent for that cycle?
I was thinking of creating a Totals query to count who has the least
number of frequent interactions and sort in ascending order, and using
that order to do the looping append query, so that those with the
lowest number of interactions are selected first, but I'm not sure
where to go next.
Some suggestions would be greatly appreciated.
Thanks
I am looking to create a small peer review database that will provide
a random selection of 3 people each semester (6 month period) who will
provide a peer review for each staff member. We currentIy have 70
staff. I have created the following tables"
tblStaff - IDStaff (Autonumber) FirstName, LastName, EMail
tblFrequentInteraction - IDInteractionF (Autonumber), IDStaff (Long),
IDFrequent (Long), IDCycle (long) which stores the staff ID's and the
ID's of those people with whom they frequently interact (minimum of 6
people per staff, per cycle, but can be more)
tblReviewFrequent - IDReview (Autonumber), IDStaff(long)
IDFrequent(Long), IDCyle(long) to store the selection for peer review.
I can create the append query to randomly select 3 staff from the
tblFrequentInteraction to review each staff, but we would like to
further restrict this selection so that each staff member doesn't
review more than 3 people per cycle.
My question is how do I prevent staff from being randomly selected,
once they appear 3 times in the tblReviewFrequent for that cycle?
I was thinking of creating a Totals query to count who has the least
number of frequent interactions and sort in ascending order, and using
that order to do the looping append query, so that those with the
lowest number of interactions are selected first, but I'm not sure
where to go next.
Some suggestions would be greatly appreciated.
Thanks