Hi,

This is running on a SQL Server / ASP

I have a table that keeps track of team members, I capture the normal name,
address, email fields along with special fields that track which roles
they can work in. These are some of the fields: (there are 19 total)

Project Manager:
Program Holder:
Editor:
Media Dev:
SME:
Reviewer:

When a person is entered into the system each role has a dropdown with the
choices: Yes or No , these values are entered into there own respective
fields in the table. Each peson can have multiple roles, I guess in reality the most roles a single peson could have is 5-8.

Now the questions: When I create a project there will be up to 19
roles/dropdowns to assign a person to a role according to the YES or NO in
the respective fields (example: the Project Manager dropdown will only show people with a YES in the Project Manager role field). I can easily create 19 queries and use each query to
dynamically populate the respective dropdown, but that seems like a waste of
resources.

Can anyone think of a way to use only 1 Recordset to be able to do what I
need to do. The answer is probably staring at me in my face but my brain is
finally tired of trying to figure it out.

Thanks!

Mitch

It seems that you need to normalize your database. Is all that in one table? Think about it this way... You have 19 fields containing data for everyone -- even those that only have one role. Thats a waste.

If you are still in the design phase, I suggest breaking out the roles into their own table (this way you could easily add more later). Then do a linking table that links PersonID's to RoleID's.


This will make your add/edit pages more involved, but its a better design. I would suggest using stored procedures for their maintenance.

As for your question, does this page simply assign one person to one role? Or, does it assign a person to each of the 19 roles?

Thanks for the response, here is some additional information to clarify:

Take a look at this quick screen capture (I modified an existing form to
make my point, the real one is not built yet)
www.fotash.com/images/projecttrack.gif

As you can see, I need to populate all 19 dropdowns at 1 time with people
that are qualified to take on a particular role/responsibility.

Example: Jane Smith has been flagged in the system as being able to do:
- Marketing Printer
- Marketing Copywriter
- Marketing Designer

Her name would then appear as a possible choice in those 3 dropdowns.

Does that make sense? That's why I was mentioning having to create 19
different recordsets, one for each dropdown.

Thanks...Mitch

I see.. Off the cuff, I'd say probably 19 separate ones. Particularly since a person can hold multiple roles.

Yeah...that's what I thought, I was just trying to come up with a way to avoid having to do 19 of them.

Thanks for trying...Mitch

When in doubt...reboot!