Results 1 to 6 of 6
  1. #1
    Join Date
    May 2009
    Posts
    4

    Unanswered: Grouping from several comparisons

    Hello,

    I would like to identify groups that are totally independants.

    In this example, there would be 2 groups:

    -Albert, Alex and Claude that have one-to-one features in common: the eyes ( green) and/or hair (black) and/or nose(long) IN GROUP 1
    -Audrey and Claire that have blonde hair IN GROUP 2

    FIRST_NAME_09-->CODE_09------------->GROUP

    ALBERT--> EYES_GREEN------------>1
    ALBERT--> HAIR_BLACK------------>1
    ALEX----> NOSE_LONG------------>1
    ALEX----> EYES_GREEN------------>1
    ALEX----> HAIR_BLACK------------>1
    AUDREY-> HAIR_BLONDE------------>2
    CLAIRE--> HAIR_BLONDE------------>2
    CLAUDE-> NOSE_LONG------------>1
    CLAUDE-> HAIR_BLACK------------>1

    Important:

    I am looking for a general solution to this problem, that can manage thousands of features.
    To make it easier, there would never interferences between 2 groups : for example one person from group 2 will never have green eyes or long nose.

    Is it possible to create through SQL/function a query that automatically compares and create groups?
    Find here database

    Thanks in advance for help!

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Apply Grouping to all the fields? Could it be that?

    I don't think I get the problem, but this is my guess as to the answer
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    What do you mean by
    that can manage thousands of features
    .

    Using you current table structure you would need to use a crosstab query but it will becomes impossible to manage.

    Altenatively,you will need to change your structure where you have a field for Name, Nose, Eyes, Hair etc. using this structure it will be easier to do your grouping.

  4. #4
    Join Date
    May 2009
    Posts
    4
    Hello,

    Thank you for your interest,

    When you say "Altenatively,you will need to change your structure where you have a field for Name, Nose, Eyes, Hair etc. using this structure it will be easier to do your grouping."

    How is it easier, can you show me an example?

    Thanks in advance

  5. #5
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388

    Re:Grouping

    I have attached a sample database. Have look at the structure and data in tbl_persons. I have added some sample queries in qry_grouping it gives the overall groupings.
    Attached Files Attached Files

  6. #6
    Join Date
    May 2009
    Posts
    4
    Hello,

    Finally I was helped by a friend who fixed it through a module.

    In case some are interested, find it below ;-)

    -------------------------------------------------

    Option Compare Database
    Const TableName = "BLOUB"
    Const Code = "CODE"
    Const Name = "PRENOM"
    Const GRP = "GRP"


    'Instruction: Please change the column names above before your run the program.


    Sub Test()


    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim RecordNumber As Integer

    Dim CodeRecord As String
    Dim NameRecord As String
    Dim SQL As String
    Dim GroupRecord As Variant
    Dim WinningGroup As Variant

    Set db = CurrentDb()
    Set rs1 = db.OpenRecordset(TableName, dbOpenDynaset)




    DoCmd.SetWarnings False

    'SQL = "UPDATE Grouping_function SET Grouping_function.GRP = Null;"
    SQL = "UPDATE " & TableName & " SET " & TableName & ".GRP = Null;"
    DoCmd****nSQL (SQL)

    rs1.MoveFirst
    Do Until rs1.EOF


    CodeRecord = rs1.Fields.Item(Code)
    NameRecord = rs1.Fields.Item(Name)

    RecordNumber = rs1.AbsolutePosition


    GroupRecord = FindInRecordset(CodeRecord, NameRecord)

    If IsNull(GroupRecord) Then
    rs1.Edit
    rs1.Fields.Item("GRP") = RecordNumber
    rs1.Update

    Else
    rs1.Edit
    rs1.Fields.Item("GRP") = GroupRecord
    rs1.Update
    End If


    rs1.MoveNext

    Loop

    rs1.MoveFirst
    Do Until rs1.EOF
    CodeRecord = rs1.Fields.Item(Code)
    GroupRecord = rs1.Fields.Item(GRP)

    WinningGroup = ReviewGroup(CodeRecord, GroupRecord)

    rs1.MoveNext

    Loop

    DoCmd.SetWarnings True

    End Sub

    Function FindInRecordset(CodeRecord As String, NameRecord As String)
    Dim db As DAO.Database
    Dim rs2 As DAO.Recordset

    Set db = CurrentDb()
    Set rs2 = db.OpenRecordset(TableName, dbOpenDynaset)

    rs2.MoveFirst
    Do Until rs2.EOF

    If NameRecord = rs2.Fields.Item(Name) Then
    Group = rs2.Fields.Item(GRP)
    FindInRecordset = Group
    Exit Function

    End If

    If CodeRecord = rs2.Fields.Item(Code) And NameRecord <> rs2.Fields.Item(Name) Then
    GroupRecord = rs2.Fields.Item(GRP)
    FindInRecordset = GroupRecord

    Exit Function
    End If

    rs2.MoveNext
    Loop

    End Function

    Function ReviewGroup(CodeRecord As String, GroupRecord As Variant)
    Dim db As DAO.Database
    Dim rs3 As DAO.Recordset

    Set db = CurrentDb()
    Set rs3 = db.OpenRecordset(TableName, dbOpenDynaset)

    rs3.MoveFirst

    Do Until rs3.EOF
    If CodeRecord = rs3.Fields.Item(Code) Then
    If GroupRecord < rs3.Fields.Item(GRP) Then
    rs3.Edit
    rs3.Fields.Item(GRP) = GroupRecord
    rs3.Update
    ReviewGroup = GroupRecord
    Else
    GroupRecord = rs3.Fields.Item(GRP)
    End If
    End If


    rs3.MoveNext
    Loop

    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •