Help with a query

[the_grove_man]

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>

 

[John W. Vinson/MVP]

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.