Results 1 to 15 of 15
  1. #1
    AnneForumer is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Aug 2016
    Posts
    20

    Grouping based on three columns - Snaking

    Click image for larger version. 
<br /><script async src=
    Name: Grouping.png  Views: 25  Size: 14.6 KB  ID: 25907" class="thumbnail" style="float:CONFIG" />


    Please expand the picture above. I have not learned how to increase its size.

    I have an unusual need for grouping. If it was not for the unusual need, there would be separate groups for items 1 and 2 (because of common Prop1, C1); items 2,3, and 4 (because of common Prop3, N1); items 4,5, and 6 (because of common Prop1, C2; item 7 by itself; and items 8 and 9 (because of common Prop1, C4). However, Prop2 value M2 coalesces group related to C1 with group related to N1. Similarly, Prop2 value M4 coalesces group related to N1 with group related to C2, and Prop2 value of M6 extends the group to include N2. That's why G1 extends to include items 1 through 6. G2 is a group by itself. G3 is a group because of common Prop1 value of C4.

    Pseudo code would be to first group on Prop1 and Prop3 seperately. Then see if some common property in Prop2 extends the group by jumping over to the other side. This step could be repeated many times.

    I'd appreciate a solution using one or multiple queries and or VBA code. I hope I have expressed the problem clearly.

    Thank you!
    Attached Thumbnails Attached Thumbnails Grouping.png   Grouping.png  
    Last edited by AnneForumer; 09-23-2016 at 06:45 PM. Reason: Improve picture

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I hope I have expressed the problem clearly.
    not to me I understand your table, just not how you want to use it

    Suggest provide some example data and the require result from that sample data

  3. #3
    AnneForumer is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Aug 2016
    Posts
    20
    Quote Originally Posted by Ajax View Post
    not to me I understand your table, just not how you want to use it

    Suggest provide some example data and the require result from that sample data
    Group assignment in the "Group" column would be the result of this exercise.

    The table shows relationships between properties of products. The ideal relationship between these properties would be:

    Click image for larger version. 

Name:	DesiredGrouping.png 
Views:	21 
Size:	15.2 KB 
ID:	25910

    But people have to do some work on products to achieve this ideal relationships from the relationships shown in the original table. The grouping, resulting from this exercise, would let us assign each group to an individual to work on and they will do things to products to achieve the ideal grouping. We would end up with products collected into groups which have similar properties. Let's say each group has to be sold as a set of parts where one can be replaced by another but not from another group.

    I hope this makes it less confusing; not more.

    Thank you!

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I hope this makes it less confusing; not more
    no - what is the difference between your group column and the prop1 column other than a slight renumbering. What are props? a building? rights to a service? what are groups? This outcome seems completely different for your original post

    Unless you are prepared to be clearer about what you are talking about I don't think I can help. e.g.

    'I have 9 people who all manage three properties, sometimes they share the management of a property with someone else....etc'

  5. #5
    AnneForumer is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Aug 2016
    Posts
    20
    Here is a scenario: Data on the left is what I start with and on the right is the output. Last column is what the query/vba script in question will generate.

    I'd like to be able to mark each row with an "Extended Family" designation. There are three extended families, EF1, EF2, and EF3 in this example. A row becomes part of an EF if either the mother and/or father is same. A blank cell represents "unknown". EF1 is an extended family because Zack and Yolanda's father is Albert and Yolanda, Bill, Stacie, and Colin's mother is Teresa. A child extends a connection. EF1 could also be worked starting from Dave rather than Albert. Similarly, for EF2, both Allen and Tracy have same father, Bob.

    I hope that is a better illustration.

    Thank you!

    Click image for larger version. 

Name:	ExtendedFamily.png 
Views:	9 
Size:	19.3 KB 
ID:	26061

  6. #6
    Bulzie is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    How do you know that Theresa is 1 person? Same for all the names, how do you know those are not multiple folks with same names? Also, is the Extended Family refer to the Child, so you are trying to say if they have an extended family? Can you explain more what the difference is in EF1, EF2 EF3. How would a program go line by line to mark them as such.

  7. #7
    AnneForumer is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Aug 2016
    Posts
    20
    How would a program go line by line to mark them as such.
    That is the help I'm requesting.

    How do you know that Theresa is 1 person?
    Each name is guaranteed to be unique.

    Also, is the Extended Family refer to the Child, so you are trying to say if they have an extended family?
    "Extended Family" refers to a particular set of rows. This set can be as small as one row.

    Can you explain more what the difference is in EF1, EF2 EF3.
    EF1, EF2, and EF3 have totally different mother or father.

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    OK - I think I see where you are coming from but still not certain about the logic

    I don't think it can be done with the data available - best I can come up with is

    Code:
    SELECT DISTINCT children.Child, fathers.Child, mothers.Child, children.Father, children.Mother
    FROM (tblPeople AS children LEFT JOIN tblPeople AS fathers ON children.Father = fathers.Father) LEFT JOIN tblPeople AS mothers ON children.Mother = mothers.Mother;
    which shows all the relationships, but I can see no logic for combining 'albert/teresa' with 'dave/teresa' and 'albert/blank' into one extended family - what if Stacy's mother was Tiara rather than Teresa? - would this mean that 'albert/teresa' would also be combined with 'Bob/Tiara' and 'Bob/Holly'?

  9. #9
    AnneForumer is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Aug 2016
    Posts
    20
    Yes. This is what it would look like then:

    Click image for larger version. 

Name:	ExtendedFamily2.png 
Views:	9 
Size:	12.5 KB 
ID:	26063

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    well as I said, I can see no logic to summarise these as one group with the data available. I can see what you are trying to achieve but don't think SQL is up to the job, you probably need a different type of database based on genealogy algorithms. You may be able to use vba and a recursive function of some sort.

    It would probably help if your data was normalised to a higher level e.g.

    Child...ParentType...ParentName

    But since you have said names cannot be duplicated I suspect this is just an analogy to a different problem. So at this stage I will just say good luck with your project, but I don't believe I can provide any more help.

  11. #11
    Bulzie is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    I'm still confused by how you are assigning the EF1, EF2, Ef3. It might be simple right there in front of me but I still can't see it. Please explain exactly why each line is EF1, EF2 or EF3. How high can the EF# go?

    You could probably do this analysis in VBA by reading through the records and analyzing values as you go but as I said, still confused by how you assign those EF values.

  12. #12
    AnneForumer is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Aug 2016
    Posts
    20
    Highest value of n in EFn is number of rows in case every father/mother gave birth to only one child. One way to think this might be: Each Father and Mother is a vertical bar. Each child connects these bars horizontally and the structure grows. Each EFn is a label to each such free-standing structure.

  13. #13
    AnneForumer is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Aug 2016
    Posts
    20
    Yes, it is an analogy but represents the real situation exactly. Real domain will just make the situation more difficult to explain and understand.

    I do appreciate you trying and your conclusion is very valuable in that I can give up trying to come up with a solution with my much lower level expertise.

    Thank you!

  14. #14
    Bulzie is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    So in the last 2 examples in your posts,
    Albert, Zack = EF1
    Albert, Yolanda, Theresa = EF1

    and in last example

    Albert, Zack = EF2
    Albert, Yolanda, Theresa = EF2

    What changed for this. One of Theresa names was changed to Tiara but why would that change EF1 to EF2 for Albert? I know its probably something simple I am not getting Someone help, driving me nuts now.

  15. #15
    AnneForumer is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Aug 2016
    Posts
    20
    It could have been EF1 or EF2, but only one of them. EFn labels are not important; only who are in that group. It's like two companies, A and B, merging; merged name may be either A, B, or an entirely new one, say X.

    Thank you for trying!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 12-29-2015, 04:06 PM
  2. Replies: 2
    Last Post: 10-26-2015, 06:14 AM
  3. grouping based on dob
    By msnarayanan in forum Forms
    Replies: 3
    Last Post: 09-24-2015, 12:06 PM
  4. Grouping Columns with dates
    By gauravnagpal in forum Access
    Replies: 6
    Last Post: 04-10-2012, 06:03 AM
  5. Grouping and Transapose rows with columns
    By HendriX99 in forum Queries
    Replies: 3
    Last Post: 03-14-2011, 03:23 PM

Tags for this Thread

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