Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147

    Access SQL for Recursion

    I am new to using SQL and Access (sort of). I know that this is a Recursion (Hierarchical, Parent-Child, Tree, BofM) type problem and that Access does not handle this well.

    The question is how to write the query to join the tables together? Both UnitFK and SubUnitFK have a relationship (1 to M) with UnitID.


    UnitID Title Description
    1
    People
    2 Team


    3 Pod
    4 Group
    5 Floor
    6 Dept Department

    SubUnit_ID UnitFK Qty SubUnitFK
    1 2 2 1 Team has 2 people
    2 3 2 2 Pod has 4 people in 2 teams
    3 4 4 3 Group has 4 Pods
    4 4 1 1 Group also has a team, 18 people
    5 5 4 4 Floor has 4 Groups
    6 5 1 3 Floor also has a Pod, 76 people
    7 6 2 5 Dept has 2 Floors
    8 6 1 2 Dept also has a Team, 156 people

    I hope this makes sense

    Thanks for Looking
    Last edited by Western_Neil; 01-18-2019 at 07:36 PM. Reason: Changed 5 & 6 to read Floor, 8 to Team

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    If I understand you correctly add the first table and 2 copies of the second table to a query
    Join UnitID to UnitFK in one copy & to SubUnitFK in the other.

    Click image for larger version. 

Name:	QueryDesign.PNG 
Views:	48 
Size:	19.2 KB 
ID:	37005

    Click image for larger version. 

Name:	QueryResults.PNG 
Views:	47 
Size:	19.5 KB 
ID:	37006

    Hope this helps. Modify as necessary
    Attached Files Attached Files
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Not the way to do it.
    instead youd have a table for each:

    tDept
    Acctg
    HR
    I.T.

    tDeptLoc
    ------------
    deptname, floor
    acctg, 1
    acctg, 2
    HR, 2

    tEmployees
    EmpID, Dept
    12, Acctg
    32, HR

    no need for recursion.

  4. #4
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    I think I can get the SQL code for this from this.
    Any idea of how deep this will go?

    Thanks ridders52

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    access sql does not do recursion, you will need to use vba code to create the required recordset. Either way, I don't think you need recursion, if you did then subunitFK would join to SubUnit_ID - unless you are missing a field?

    Your example is confusing - from your description it looks like SubUnit_IDs 3 and 4 belong to the same group so there needs to be another 'something' that joins them together. Suspect your SubUnit_ID 5 and 6 should be floor and not group. So SubUnit_ID 5 (UnitFK=5 floor?) has 4 (qty) x 4 groups. SubUnitFK seems to link to SubUnit_ID so that says SubUnit_ID 5 has 4 groups of 1 team of 2 people. Or perhaps SubUnitFK links to UnitID, in which case SubUnit_ID 4 has one person, not a team. And with regards SubUnit_ID 8 - doesn't a pod have to be on a floor? And isn't 2 Team?

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Neil
    Its not clear whether you are answering Ranman or myself
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    Ajax
    Quote Originally Posted by Ajax View Post
    access sql does not do recursion, you will need to use vba code to create the required recordset. Either way, I don't think you need recursion, if you did then subunitFK would join to SubUnit_ID - unless you are missing a field?
    Neil> MS Access will not allow a relationship with itself (I have tried). In a query you can (from what I understand), but that is only temporary, no checking. So using using two tables referring to each other seems to the way around (I hope). But how to make it work?
    I think that SQL can be used in VBA, so you are saying that VBA is needed to hold the
    temporary results of the recursion?>

    Quote Originally Posted by Ajax View Post
    Your example is confusing - from your description it looks like SubUnit_IDs 3 and 4 belong to the same group so there needs to be another 'something' that joins them together.
    Neil> I was trying to show that a (Sub)Unit could have many parts, being 4 pods (of 4 people) and a team (of 2) making 18 people >
    Quote Originally Posted by Ajax View Post
    Suspect your SubUnit_ID 5 and 6 should be floor and not group.
    Neil> Opps, correct and corrected>

    Quote Originally Posted by Ajax View Post
    So SubUnit_ID 5 (UnitFK=5 floor?) has 4 (qty) x 4 groups. SubUnitFK seems to link to SubUnit_ID so that says SubUnit_ID 5 has 4 groups of 1 team of 2 people. Or perhaps SubUnitFK links to UnitID, in which case SubUnit_ID 4 has one person, not a team. And with regards SubUnit_ID 8 - doesn't a pod have to be on a floor? And isn't 2 Team?
    Neil> This is what happens when you make up examples. No I meant that a Dept. has an additional team, not that it matters really. tblUnits are organizations not locations (I see the confusion now). I think you missed "Both UnitFK and SubUnitFK have a relationship (1 to M) with UnitID"

    Colin has given me a strong hint that I need to play with tonight. Alway open for new ideas, slants and methods, and masters should be listen to for thay have experience.
    Thanks for looking and trying to understand.

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Neil
    As I'm not totally clear what your real life data shows, I wasn't convinced that the approach I suggested was what you needed.
    To be honest I'm still not.

    However I've played around some more & offer more examples for you to look at (and mostly discard)

    Query1 - as in original answer (9 records)

    Query2A - left join Table1-> Table2 - exactly the same results as Query1 (9 records) !!!
    Query2B - right join Table2 -> Query2A - different results (7 records)

    Query3 - double inner join (6 records)

    Query4A - left join Table2-> Table1 - different results (8 records)
    Query4B - left join Table2 -> Query4A - exactly the same results as Query3 (6 records) !!!

    The point I'm trying to make is that you need to be very careful that the results are what they should be
    I'm not suggesting any of these are actually correct.

    Hope this helps rather than just causes confusion
    Attached Files Attached Files
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Neil> This is what happens when you make up examples. No I meant that a Dept. has an additional team, not that it matters really. tblUnits are organizations not locations (I see the confusion now). I think you missed "Both UnitFK and SubUnitFK have a relationship (1 to M) with UnitID"
    no I didn't , just assumed you had that the wrong way round - unitID has a 1-M relationship with UnitFK and SubUnitFK
    Neil> MS Access will not allow a relationship with itself (I have tried).
    it does

    TblName
    TblNamePK
    Desc
    TblNameFK

    Code:
    SELECT *
    FROM
    TblName as P INNER JOIN tblName AS C ON P.TblNamePK=C.TblNamePK
    it doesn't help that your field names imply a relationship along these lines which, together with your thread title, implies a recursive relationship

  10. #10
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    been giving this some thought - proably better if you explain what you want to do rather the ask how to fix what you are doing. I suspect what you need is something like:

    tblTypes
    TypeFK...TypeDesc
    1...………..person
    2...………..platoon
    3...………..battalion
    4...………..army

    then you need to start from the top - a parent record needs to exist before the child record

    tblUnits
    UnitPK...UnitName......TypeFK..Qty...UnitFK
    1...……...7th Army.......4...……..null...null - nulls because this is the top level
    2...……...1st Battalion...3...…….1...….1 - 7th Army has 1 battalion
    3...……...Platoon A.……..2...…...3...….2 - 1st Battalion has 3 platoon A's
    4...……….infantryman....1...…...20....3 - platoon A's each have 20 infantrymen
    5...……...Platoon B...…...2...…..1......1 - Ist Battalion also has 1 Platoon B
    6...……...infantryman.....1...…..10....5 - platoon B's have 10 infantrymen
    7...……..snipers...………….1...…..10....5 - platoon B's also have 10 snipers
    8...………Infantryman.....1...…...5.....1 - 7th Army also has 7 infantrymen
    etc

    So now you can see the 7th Army has 1 battalion consisting of two platoons, one with 20 infantry men, the other with 10 infantrymen and 10 snipers plus a further 5 infantrymen

  11. #11
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    Yes that is correct. It is interesting to see a crazy garbage output that is correct in concept Now I understand to do this, you are doing a recursion within the query without a formal relationship being stated, (no rules) correct?

    A couple of general questions:
    1) In record 1 does the UnitFK value need to null or could it value
    assigned (to show Top of Structure) ? ( I don't like nulls because they can mean 4 things, Not used, Unknown value, To lazy to input, or a Special Marker)
    2) Does the structure have to be Top Down? It's hard to make a organization smaller than a person.
    3) "a parent record needs to exist before the child record" Does it? I thought it just had to exist. are you saying that UnitPk has meaning? (besides being just a ID marker)

    My design uses two tables so that I can have that formal relationship, and I also need to have a temporal element included (Org changes over time). Just starting out learning, I left out the temporal stuff to keep is simple, just like I'm keeping my record counts below 40. Are you saying that you would use just the query join method? and handle the other stuff later? I know a lot of things are not stated, but ...

    My head pops when I think of Outer Joins, Left & Right Joins, Double Inner Join, but I know they all have meanings and are different (got to be joke in there somewhere). I come from a COBOL/RDMS background (25 yrs ago) so VBA and SQL are similar but very different (Basically wrote scripts for reports). Logic of the code is no problem, the syntax is , and what calls are available. (Graphical interface to write code ???). It's all on a learning curve, and some leaps are needed.

    Now where are we?

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    See if this article on my website helps you understand about the different join types http://www.mendipdatasystems.co.uk/q...pes/4594517491
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  13. #13
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    1) In record 1 does the UnitFK value need to null or could it value assigned (to show Top of Structure) ?
    No, you could use 0 or perhaps a negative number if all your PK's are positive. My preference is nulls because you can exclude them from the index making for a more efficient index.
    2) Does the structure have to be Top Down? It's hard to make a organization smaller than a person.
    I would say yes - but the top might be betallion until you have an army to assign it to
    3) "a parent record needs to exist before the child record" Does it? I thought it just had to exist. are you saying that UnitPk has meaning? (besides being just a ID marker)
    unitPK has no meaning other than as a record identifier. If you create a child record and a parent does not exist - the UnitFK would be null/0/negative - making it a 'top' record

  14. #14
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    Ajax
    What I was trying to do is code a TOE (Tables of Organization and Equipment) to help me get solid on Access. This is one part of my overall design which why the tables start with E_ ( I will probably remove that latter).
    Click image for larger version. 

Name:	E_Tables 190119.png 
Views:	39 
Size:	46.7 KB 
ID:	37016
    I have also attached a quick schema of what I thinking at this time . Now this is my design and it could be total wrong (or right) (Note that the temporal element is missing). I have decided to stop designing and start coding. Now I am finding the pond to be a little deeper than I expected .

    For the TOE example (Note the Hot link in TOE) has 4 levels with 8 elements giving 104 people and the 19 types of equipment assigned. This is what I'm trying for. I know that it's almost a bill of material but with twists (now thats not new ). Also note that for this example both TOE 7-11 and 7-1 call on this TOE (7-12).

    What I was looking for was the VBA/SQL code to tie E_tblElements and E_tblSubElmts together getting all the sub elements. I think you have been saying that it is a simple join (head pops), maybe it is? The rest I think are simple joins which I should be able to do.

    Thanks for Looking


  15. #15
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    Ajax
    Thanks for the reply. Hmm a negative number is interesting thought. The other two are okay, that is what I was thinking the answer was, but I wasn't sure.

    Thanks
    Last edited by Western_Neil; 01-19-2019 at 03:15 PM. Reason: Grammer

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Custom Classes/Objects - Recursion via ByRef & ByVal
    By joelmeaders in forum Programming
    Replies: 14
    Last Post: 08-31-2016, 07:53 PM
  2. Consecutive without recursion.
    By emihir0 in forum Queries
    Replies: 7
    Last Post: 10-28-2015, 06:22 PM
  3. recursion in Access?
    By DB88 in forum Access
    Replies: 18
    Last Post: 05-21-2014, 01:51 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums