Full Version: Many to Many
UtterAccess Discussion Forums > Microsoft® Access > Access Tables + Relationships
gcoons
Needed a quick opinion on structure of something.

I am trying set up a section of my db to track the materials that go to a field job. Each field job can have ultiple materials, and multiple field jobs can have the same materials. I am assuming that this implies a many to many relationship. As I understand it would have to look something like this.

tblFieldJobs
Field_Job_ID (PK)

tblMaterial_Router
Router_ID (PK)
Field_Job_ID (FK) (lookup)
Material_ID (FK) (lookup)

tblMaterial
Material_ID (PK)
Material_Type_ID (FK) (lookup)
Material
Material_Cost

tblMaterial_Type
Material_Type_ID (PK)
Material_Type

My problem is this:
1) I do not know where to add the quantity of the materials, should it be on the router table f the field jobs table? I would like this quantity able to multiply by the cost of the material.
2) I do not know how to display this information, am I supposed to build a query off tblMaterial_Routerr tblmaterials and tblmaterial_type, then do a list box? or is a listbox based on tblMaterial_Router enough?
fkegley
1) As I understand it, tblMaterial_Router is SPECIFIC to a job. So it seems to me that the quentity should be in that table. If the cost of the material is the same for every job, then you would get it from the tblMaterial table. If not, then the cost would have to be in the tblMaterial_Router table also.

2) I think I would develop a form based on tblFieldJobs, it would contain a subform control that contained a form based on tblMaterial_Router. tblMaterial and tblMaterial_Type would serve as the row sources for combo boxes and/or list boxes.
gcoons
Thank you frank, that was the direction I was headed in but I wanted to get my mind straight on it before I dug myself to deep into confusion.
fkegley
Greg, you're welcome. I am glad I was able to help.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.