[madctch]

Hello all,
I have a db where I have a list of projects. The projects are separated into two tables with the first containing information about the client and a related table with information pertaining to the individual projects done for each client. This is a one to many relationship.

I am currently trying to establish a resume creator for my db where I would have an employee table which would be related to individual client's projects. My goal is to create a form that I can select what projects each employee worked on and give a report on all the information given in the client and project tables for the projects any particular employee worked on.

What I cannot figure out is how to relate the employee table. I can't do it to just the client table because the employee is not on every project the client has and I cannot relate it just to the project table because I need the client information in my report as well.

Any help would be greatly appreciated,
Madctch

[OBP]

Mad, you need a "many to many" table, which is really a 2 sided One to many table.
In the table you would just have the EmployeeID, the ClientID and the ProjectID.
In this way you can mix and match them as much as you like.
You bring the data together in a Query based on the many to many table and add the other tables to it to get the data like names etc.

[madctch]

Is the many to many a completely different table or is it part of the employee table and how are the relationships set up? I attached a db with a simplified version of my db if that helps.

[OBP]

Mad, your "Linking Table" only actually needs the Project and Employee IDs in as you already have a link form Client to Project.
I have created some queries and forms to demonstrate how the data from the Linking table can be displayed 2 ways, the first is the "Projects" Form, this allows you to Enter or Select a Project and then Select a Client and then select as many employees in the "Linking" Subform as you want. Note that there are 2 Projects for 2 Clients, and 3 employees, only Barney Rubble appears on both projects.
Now if you look at the Employee resume form I have reversed it, you can select an Employee and see which Project(s) they have worked on. The Employee Projects Subform is using the Linking Table Query as the Employee one on the Project Form, I have just changed the Fields to show the opposite data.

If you want the Database to be "Client" orientated, rather than Project orientated then I would create a Client Mainform which has the current Project Form on it as a "Subform" and remove the Client Combo from the Project Form.
Thus you would select a Client, then project and then an employee.

I hope this makes some sense to you.

[madctch]

Thanks a bunch