Many-to-Many tables/relationships

[yubin]

I am making a DB that consists of 3 tables.
1. Users: UserID/LastName/FirstName
2. Projects: ProjID/ProjNumber/Comments
3. Assigned: ProjID/UserID
I already setup the relationship between them. I've never had to work with linking tables and I am not even sure this is where I would use one. I need to be able to create a form that will allow me to search by user/users and/or ProjNumber. I'm just not sure how the tables will be updated automatically.

Now multiple people can be on one project and One person can have multiple projects. On top of this, I have to track the hours that each person puts into each project and be able to total the hours per project and/or person. It's a pretty simple idea, but is kicking my rear due to my somewhat limited knowledge with access.
BTW I'm working with MS access 2003. I've searched the internet and found many tips useful, but haven't found any good documentation that I can relate too in this circumstance. Any advice or recommended links would be great. Thanks.

 

[fneily]

Here's something to read:
Fundamentals of Relational Database Design

http://r937.com/relational.html

So far so good. You have three tables - tblUsers, tblProject, and tblAssign. A many-to-many relationship between tblUsers and tblProjects. The tblAssigned is called a junction table and, as you rightly have, contains the primary keys of the other two tables. You may want to give it its own primary key AssignID, or you could have a multi-field key with ProjID and UserID. Any COMMON fields are also in this table. Such as HoursWorked. Now, by combining the tables through a query, you can get reports on the hours worked by a person, on a single project, total hours of everyone on a project, etc.
So you can fill in the tblUsers, fill in the tblProject and then use comboboxes on a form that are bound to the Id fields in the tblAssign table to fill in that table.