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
|
|||
|
|||
many to many self join
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. |
#2
|
|||
|
|||
many to many self join
On Sep 24, 2:46*pm, 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. Out of time, but this should probably continue with a tbl_contains table for a many-to-many relationship: Project_ID,Contains_ID,Parent_ID,Child_ID 1,1,'router1','box1' 1,2,'box1','feet1' 1,3,'box1','button1' etc. primary key(Project_ID,Contains_ID) nonunique index (Parent_ID) nonunique index (Child_ID) To get full dataset in each row by project you may need to use a union with increasing levels of self joins in each select. Hopefully you don't need to see the data that way, but it's possible. Jeff |
#3
|
|||
|
|||
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: 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" wrote in message ... 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 wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|