Create table method?

[dev_gordie]

Hello Accessors -

I am working on a new database and am already running into a design dilemma. I welcome any and all ideas for getting past this obstacle. Please keep in mind that I'm a bit of a newbie at this so my problem may be really mundane.

I have a table that lists information about my company. I have another table that lists various comittees and their subcomittees within the company and stats and facts about those. These are added to as needed. I have another table that is dedicated to employee information.

I want to create a third table that "populates" the comittees with staff members as they join a comittee. Many of the same employees belong to multiple comittees. Maybe this isn't the most efficient way to work with this data and relying on queries and I would like to have that input as well.

Thanks for any and all help with this.

dev_gordie

 

[Mile-O]

kay, you have two tables just now:

tblEmployees and tblCommittees

tblEmployees
EmployeeID
Forename
Surname
etc...


tblCommittees
CommitteeID
Committee
etc...

The new table you need is:

tblEmployeesToCommittees
EmployeeID
CommitteeID

those two fields are foreign keys to the previous two tables. They should also be the primary key of that table.


Connect all the realtionships up: EmployeesID to EmployeeID and CommitteID to CommitteID and you have two one-to-many relationships. These simulate a many-to-many relationship.