Table setup - Is my thinking correct

[tmulrooney]

I have created two tables. One is call "employees" the other is called "projects". I setup a form that links the two tables by setting the master and child fileds to the same field. The form is working correctly. AS I scroll through my projects I can see the assigned employees to each project. I created a separate form called Employee data. It calls information from the employee table. My problem is.....The employee table is creating multiple employees entries based on the number of projects they are associated with. I want my employee table to have one entry for each employee.

Should I have created a table called employees. A table called Projects. Then create a third table where the data of the two linked are stored? Can you put me on the right track?

Thanks,
Tim

 

[llkhoutx]

You have stumbled onto a one to many relationship. One employee, many projects.

Yes, create a third table (a junction table) which has EmployeeIDs and ProjectIDs, with duplicates being allowed.

 

[Mile-O]

You have stumbled onto a one to many relationship. One employee, many projects.

many-to-many

One project can have many employees;
One employee can belong to many projects.

 

[tmulrooney]

In my "Employees" Table I have:
Employee - Autonumber
Lastname - text
Firstname - text

In my "Projects" Table I have:
ProjectID - Autonumber
Projectname - text
Projectnumber - number

In my juntion table "projectjunction"
EmployeeID - Autonumber?
ProjectID - Autonumber?

Do I make these Autonumbers?
Then I go to Relationships and setup the relationships.
What would my linkings look like? One from each table to the junction table? (total of two relationships)

 

[Mile-O]

Have a look at my attachment in this thread

 

[Mile-O]

The junction table uses foreign keys from the other tables.

 

[llkhoutx]

Your juntion table field types should be Long, not autonumber.

 

[tmulrooney]

I am on the right track. Thanks so much. I have to figure out how to setup a form to pick from the employees and assign to the projects. The tables are linked and look right.

Thank you so much!

Tim

 

[tmulrooney]

The new setup is working but I am back to the same problem I had before. Everytime I assign an employee to a project it stores another entry in my employee table. I really don't care except when I open my employee form to edit information about each employee I now have several entries for one employee.

Does that make since?

 

[Tiro Cupidus]

Sounds like something is bound to a field in your Employees table when it should be bound to the corresponding field in the junction table.

 

[tmulrooney]

I have an employee subform using the employee table as its source. Should I link the employee subform to the junction table? If so, I don't have any employee information in the junction table. How do I copy all of my data from "employee" and "projects" into the junction table?

 

[Mile-O]

You shouldn't be binding a table to the form. Use a query to link the tables and base your subform upon that.

 

[tmulrooney]

I am feeling like a complete"re-re" I am not sure why I cannot understand this.

In my employee table I have
EmployeeID
Lastname
FirstName

In my Projects table I have
ProjectID
ProjectNo
Projectname

In my junction table I Have
EmployeeID
ProjectID

My relationships are right.

How can I make a query of the junction table if there is no data. I have data in the employee table. I have data in the projects table. I setup a query with four columns:
EmployeeID from Junction table
ProjectID from Junction table
lastname from employee table
firstname from employee table.

I don't see any information. Do you see my disconnect?

 

[Pat Hartman]

You need to change the join type from inner to left. That will give you employees that have not been assigned to projects as well as those that have.

Take a look at my many-to-many sample db. It has forms and queries which may make the process clearer.

Many-to-Many

 

[tmulrooney]

Your example is very similar to what I am trying to accomplish. I am going to play around in it for a little while. Thank you!

 

[tmulrooney]

Thank You, Thank You, Thank You!
It is working!