H
hovelbeast
I have a table of volunteers with the following fields:
Volunteer (autonumber primary key)
FirstName
LastName
CellPhone
OfficePhone
Credential (autolook up with 4 different variables/skills)
Available (simple yes / no)
I would like to build volunteer teams directly out of this table based on
the 4 possible credentials of each volunteer. I need to build a query or
series of queries (perhaps one for each team) that gives me a specific number
of volunteers with Credential 'A', a specific number of volunteers with
Credential 'B', or better yet a proportion of credential criteria, with a
limit of no more than 10 volunteers on each team, selected from among those
listed in my data table as being 'Available = yes'. In other words, there
could be teams with 4 volunteers meeting credential A and 1 volunteer meeting
credential B, or teams with 2 volunteers meeting credential A and 8
volunteers meeting credential B... (same 4 to 1 ratio).
Each team must have unique volunteers, ie the same volunteer cannot appear
in both team 1 and team 2. Ideally it would also be flexible, in case one
volunteer is unable to make it at the last second, he/she would be
automatically replaced by someone meeting the credential criteria.
Thanks.
Volunteer (autonumber primary key)
FirstName
LastName
CellPhone
OfficePhone
Credential (autolook up with 4 different variables/skills)
Available (simple yes / no)
I would like to build volunteer teams directly out of this table based on
the 4 possible credentials of each volunteer. I need to build a query or
series of queries (perhaps one for each team) that gives me a specific number
of volunteers with Credential 'A', a specific number of volunteers with
Credential 'B', or better yet a proportion of credential criteria, with a
limit of no more than 10 volunteers on each team, selected from among those
listed in my data table as being 'Available = yes'. In other words, there
could be teams with 4 volunteers meeting credential A and 1 volunteer meeting
credential B, or teams with 2 volunteers meeting credential A and 8
volunteers meeting credential B... (same 4 to 1 ratio).
Each team must have unique volunteers, ie the same volunteer cannot appear
in both team 1 and team 2. Ideally it would also be flexible, in case one
volunteer is unable to make it at the last second, he/she would be
automatically replaced by someone meeting the credential criteria.
Thanks.