many to many self join

Thadson this post helped me

as well as this sample DB
Here's a sample database that illustrates the technique:
http://www.mvps.org/access/modules/mdl0027.htm

Subject: Re: Grouping components and updating locations 10/10/2008 8:34 AM
PST

By: Barry A&P In: microsoft.public.access.tablesdbdesign


Allen
this query worked for me

SELECT Parent3PartNumbers.[Part Number], Parent3PartNumbers.Description,
Parent3.[Serial Number], Parent2PartNumbers.[Part Number],
Parent2PartNumbers.Description, Parent2.[Serial Number],
Parent1PartNumbers.[Part Number], Parent1PartNumbers.Description,
Parent1.[Serial Number], PartNumbers.[Part Number], PartNumbers.Description,
Parts.[Serial Number], PartNumbers.PartNumberID
FROM ((PartNumbers AS Parent2PartNumbers RIGHT JOIN (PartNumbers AS
Parent3PartNumbers RIGHT JOIN (((SerialNumbers AS Parts LEFT JOIN
SerialNumbers AS Parent1 ON Parts.SNParentID = Parent1.SerialNumberID) LEFT
JOIN SerialNumbers AS Parent2 ON Parent1.SNParentID = Parent2.SerialNumberID)
LEFT JOIN SerialNumbers AS Parent3 ON Parent2.SNParentID =
Parent3.SerialNumberID) ON Parent3PartNumbers.PartNumberID =
Parent3.PartNumberID) ON Parent2PartNumbers.PartNumberID =
Parent2.PartNumberID) LEFT JOIN PartNumbers AS Parent1PartNumbers ON
Parent1.PartNumberID = Parent1PartNumbers.PartNumberID) LEFT JOIN PartNumbers
ON Parts.PartNumberID = PartNumbers.PartNumberID
ORDER BY Parent3PartNumbers.[Part Number], Parent3.[Serial Number],
Parent2PartNumbers.[Part Number], Parent2.[Serial Number],
Parent1PartNumbers.[Part Number], Parent1.[Serial Number], PartNumbers.[Part
Number], Parts.[Serial Number];

Now i just have to figure out how to use the tree data in the query,
Thank you for getting me on the right track..



"Allen Browne" wrote:

> Hi Barry
>
> You need to use outer joins to get the records to show up.
>
> Double-click the line joining the 2 tables in the upper pane of the query
> design window. Access pops up a dialog offering 3 options. You have to
> choose either the 2nd or 3rd one (depending which way round your tables
> are), not the first one.
>
> The 2nd part of this article has a brief explanation of outer joins:
> The Query Lost My Records!
> at:
> http://allenbrowne.com/casu-02.html
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Barry A&P" <BarryAP DeleteThis @discussions.microsoft.com> wrote in message
> news:EA493983-5EBA-4A84-81EF-529416EF0DDD@microsoft.com...
> > Allen
> > Thank you so much for your help, I was so excited by the simplicity of
> > your
> > answer, however i could not get the query to work or the self-join. my
> > database is as follows (simplified)
> >
> > Tbl_PartNumbers
> > PartID Auto# PK
> > PartName
> > Life
> > Cost
> >
> > Tbl_SerialNumbers
> > SerNoID Auto# PK
> > PartID (Lookup to Tbl_PartNumbers)
> > SerNoparentID
> > Location
> >
> > Here is where My Query ended up
> > SELECT PartNumbers.[Part Number], PartNumbers.Description,
> > SerialNumbers.[Serial Number], Sub.PartNumberID, Sub.[Serial Number], [Sub
> > Sub].PartNumberID, [Sub Sub].[Serial Number], [Sub Sub Sub].PartNumberID,
> > [Sub Sub Sub].[Serial Number]
> > FROM PartNumbers INNER JOIN (SerialNumbers AS [Sub Sub Sub] INNER JOIN
> > (SerialNumbers AS [Sub Sub] INNER JOIN (SerialNumbers AS Sub INNER JOIN
> > SerialNumbers ON Sub.SNParentID = SerialNumbers.SerialNumberID) ON [Sub
> > Sub].SNParentID = Sub.SerialNumberID) ON [Sub Sub Sub].SNParentID = [Sub
> > Sub].SerialNumberID) ON PartNumbers.PartNumberID =
> > SerialNumbers.PartNumberID;
> >
> > It has no values when i opened it
> > am i way off base?
> >
> > The SireSireDameSire Query in your horses example was too hard for me to
> > reverse engineer. i even tried to setup tables with horses names Ect. i
> > was
> > able to get the Self-join (two tables with two one-to-manys) but the
> > query
> > asked for values when i ran it.. and didnt work.
> >
> > Thanks Again
> > Barry
> >
> > "Allen Browne" wrote:
> >
> >> One approach is to place everything in the one table, e.g.:
> >> PartID AutoNumber primary key
> >> PartName Text
> >> ParentPartID Number
> >>
> >> You put the major assemblies in the table, leaving ParentPartID blank as
> >> they are not part of anything. Next you enter he sub-assemblies, and in
> >> the
> >> ParentPartID you put the PartID of the assembly it belongs to. Next you
> >> enter each component, and in ParentPartID you enter the sub-assembly it
> >> belongs to. And so on.
> >>
> >> This structure (a self-join), copes with any level of embeddedness. It's
> >> really easy to design, really flexible. For example, you can create a
> >> related table to record sales, and its PartID field can relate back to
> >> any
> >> kind of level (that is, you can sell a component, a sub-assembly, a major
> >> assembly, whatever.)
> >>
> >> The disadvantage of this approach comes when you try to do things like
> >> building a complete family tree of the parts you offer. You can create a
> >> monster query that does this to about 4 generations, e.g.:
> >> http://allenbrowne.com/ser-06.html
> >> But the problem of infinite recursion arises, e.g. where are part is
> >> wrongly
> >> entered as its own grandparent.
> >>
> >> Here's a sample database that illustrates the technique:
> >> http://www.mvps.org/access/modules/mdl0027.htm
> >>
> >> Here's some more involved SQL approaches from Joe Celko:
> >> http://www.intelligententerprise.com/001020/celko.shtml
> >> http://www.dbmsmag.com/9603d06.html
> >> http://www.dbmsmag.com/9604d06.html
> >> http://www.dbmsmag.com/9605d06.html
> >> http://www.dbmsmag.com/9606d06.html
> >>
> >> BTW, I hear a rumour that the latest SQL Sever (Express?) supports
> >> resolving
> >> this kind of structure better than JET does, but I haven't personally
> >> tested
> >> it.
> >>
> >> "Barry A&P" <Barry A&P DeleteThis @discussions.microsoft.com> wrote in message
> >> news:64EB2884-BE63-41AF-BC73-98E6601808D1@microsoft.com...
> >> >I am working on a aircraft Parts management database. I am trying to
> >> >find a
> >> > way to relate or link Major assembly components, Sub assembly
> >> > components,
> >> > and
> >> > components. For example all parts, assemblies, and sub assemblies are
> >> > listed
> >> > in the tbl_partnumbers they have corresponding records in
> >> > tbl_serialnumbers
> >> > that also refer to a record in tbl_locations I want to be able to view
> >> > a
> >> > major-assembly record that will also list all sub-assembies and the
> >> > parts
> >> > those sub-assemblies contain. I also want to be able to move a
> >> > sub-assembly
> >> > from one Major-Assembly to another and have all of its parts Follow it.
> >> > Or
> >> > move a major-assembly between aircraft and have all sub-assemblies and
> >> > parts
> >> > follow. maybe this can be done by somehow associating the locations,
> >> > but i
> >> > am
> >> > stumped for now.
>
>


"thadson" wrote:

> I'm faced with a unique problem.
> many-to-many self join
> I have a list of drawings. some drawings include subdrawings and those
> subdrawings can have more subdrawings. However, the subdrawings can be main
> drawings for a project by themselves.
>
> It looks like this:
> I have projects. I put them into tbl_projects
> I have drawings for above projects. tbl_drawings
> now some drawings belong to some drawings.
>
> router1 - box1 - feet1
> - button1
> - mainboard - powersupplyboard1
> -antenna
>
> router2 - box1 - feet2
> - button1
> - mainboard - powersupplyboard2
> - antenna2
>
> switch1 - box1 - feet1
> - button2
> - mainboard - powersupplyboard1
>
> In plain words, a there are many routers, all can have the same or different
> looking boxes. All boxes can have the same or different feet, switches,
> mainboards, etc...
> Than all mainboards have parts on them, like capacitors and resistors, but
> can have complex parts that are described in more drawings like the
> powersupply which in turn could have its own enclosure and feet drawing that
> could be the same or different from the above. Even writing it down seems
> complex and confusing.
>
> Any idea how I can self join the drawings so any drawing can belong to any
> other drawing in any depth?
>
> Thanks for any help.