Changing to field value instead of fields.

[selvsagt]

Help needed: Large Yes/no tables!

I have a form with names of employees, and different skills that employee has.

It is listed in a form as columns with the employee name in rows, and columns for the skills. The form is in fact really great, but I see that the solution might bring problems in the future because of maintainance issues, and field limitations. I add fields frequently, and the table is growing without me beeing able to control it.

I have organized this in only one table, and used the name of the skill as a field. There is many other tables in the DB, but none of them covers this area.

exampletable:

employeeID | auto
employeename | text
Hireddate | date/time
Fireddate | date/time
baking | yes/no
cooking | yes/no
driving | yes/no
juggling | yes/no

With this solution I get the skillname as a heading, and the name as a column with lots of yes/no values. I love the filter functions, and the form is quite nice...


exampleform:

Employee baking cooking driving juggling
employeeeID1 yes no no yes
employeeeID2 no yes yes yes
employeeeID3 no no yes yes
employeeeID4 yes yes yes no

and so on.

Is there a way to organize this so that the skill name comes in the top row, the results get listed as shown above when the skill is placed as a field value, not a field itself?

I have looked for a solution with many-to-many relations with 3 tables, but cant seem to make it work right. It is especially the form I have trouble creating with the new design.
I have also tried to make crosstab queries with minor success.

Does anyone have a suggestion on how I can make the relations work.
One employee can possess a great number of different skills, and the skill can be possessed by a great number of employees. This is not a difficult issue by itself, the problem is to make the form as shown over.



Thank you, in advance.

 

[boblarson]

Here's a sample (very simple sample) that should give you some ideas.