If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help with a query
I have the following tables: [I will only show the pertinent data]
Project [RecNo, ProjectName] ProjectFile [ProjectRec, FileRec] Files [RecNo, FileName] Components [RecNo, StandardRec, FileRec, RefDes, Title] StandardTypes [RecNo, ComponentType] Schema: Project -- ProjectFile [RecNo, ProjectRec] Files -- Components [RecNo, FileRec] Files -- ProjectFile [RecNo, FileRec] StandardType -- Component [RecNo, StandardRec] I want to write a query that is as the following: SELECT a.ComponentType, b.RefDes, b.Title, T, from (((StandardType as a INNER JOIN Components b ON a.RecNo = b.StandardRec) INNER JOIN Files c ON c.FileRec = a.RecNo) INNER JOIN ProjectFile d ON c.RecNo = d.FileRec) INNER JOIN Projects e ON d.ProjectRec = e.RecNo; T = Since Files is joined to ProjectFile which is joined to Projects, we have a many to many relationship. One project has many files and one file can belong to many projects. Hope you are with me so far. Now since Files is also joined to Components, it is possible to know which projects that each component is on. So I want T to be the all the ProjectRec from ProjectFile seperated by a ",". I don't want to duplicate the components to list them all, I want to list them once, and have a column list its association [1, 2, 5, 7, 7, etc...] Does that make sense? It is like I need a for loop on T |
#2
|
|||
|
|||
Help with a query
On Wed, 16 Jul 2008 13:10:03 -0700,
wrote: So I want T to be the all the ProjectRec from ProjectFile seperated by a ",". I don't want to duplicate the components to list them all, I want to list them once, and have a column list its association [1, 2, 5, 7, 7, etc...] See the sample code at http://www.mvps.org/access/modules/mdl0004.htm which does exactly this. -- John W. Vinson/MVP |
Thread Tools | |
Display Modes | |
|
|