Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    finding if 3 out of 9 records match (A2k2,SP2,DAO)

    Hi All,
    I am wondering if there is another way to do the following:
    Table Design:
    <pre> PKID SubPKID TestType DataValue1 DataValue2
    <auto> 1 TestType1 15 <null>
    1 TestType2 7 12
    1 TestType3 4 7
    etc
    1 TestType9 33.2 21
    2 TestType1 12 14
    etc
    </pre>


    There are 9 types of tests, so TestType will be 1 of 9 values.
    DataValue1 and DataValue2 may be any number including nulls or "" (DataType as Double).

    I must be able to find where *ANY* 3 of the 9 TestTypes for a given SubPKID matches the corresponding TestTypes for any of the other SubPKID's for a chosen WeekNumber and GroupNumber.
    What I am trying to do now is to pull a Recordset for all the records for the chosen Week & Group Numbers and begin a Do While .... Loop, comparing that Recordset to another Recordset filtered to not include the record the 1st Recordset would be currently set.
    As such:
    <pre> strSQL1 = "SELECT PKID, SubPKID, TestType, DataValue1, DataValue2 "
    strSQL1 = strSQL1 & "FROM qryQualCheck "
    strSQL1 = strSQL1 & "WHERE [WeekNumber] = '" & Me!cboWeekNumber & "'"
    strSQL1 = strSQL1 & " And [GroupNumber] = '" & Me!cboGroupNumber & "'"

    Set rstSQL1 = db.OpenRecordset(strSQL1)

    Do Until rstSQL1.EOF
    'select the 2nd recordset with which to compare with
    strSQL2 = "SELECT PKID, SubPKID, TestType, DataValue1, DataValue2 "
    strSQL2 = strSQL2 & "FROM qryQualCheck "
    strSQL2 = strSQL2 & "WHERE [WeekNumber] = '" & Me!cboWeekNumber & "' "
    strSQL2 = strSQL2 & "And [GroupNumber] = '" & Me!cboGroupNumber & "'"
    strSQL2 = strSQL2 & "WHERE PKID <> " & rstSQL1!PKID

    Set rstSQL2 = db.OpenRecordset(strSQL2)

    Do Until rstSQL2.EOF
    If rstSQL1!TestType = rstSQL2!TestType Then
    If Nz(rstSQL1!DataValue1, "") = Nz(rstSQL2!DataValue1, "") _
    And Nz(rstSQL1!DataValue2, "") = Nz(rstSQL2!DataValue2, "") Then
    'this would be a match
    Else
    'this is not a match so let it loop
    End If
    Else
    'this is not a match so let it loop
    End If
    rstSQL2.MoveNext
    Loop
    rstSQL1.MoveNext
    Loop
    </pre>


    This, however is very cumbersome and I can see all sorts of problems with all the Recordsets which will need to be opened via this method.
    I obviously don't know what it may be, but there has to be an easier way to do this and I feel like I'm looking way too far away for an answer.
    (btw changing the table stucture cannot be done)
    Thank you.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: finding if 3 out of 9 records match (A2k2,SP2,DAO)

    I think this can be done without code, using queries. In order not to make them too complicated, I would do it in two steps. It can be done in design view, but I'll post the SQL because that's easier to copy.

    1. Create a query that selects the records for the chosen WeekNumber and GroupNumber. The SQL for this query looks like this:

    SELECT *
    FROM qryQualCheck
    WHERE WeekNumber = Forms!frmSomething!cboWeekNumber AND GroupNumber = Forms!fromSomething!cboGroupNumber

    Let's say you save this query as qryQualCheckFiltered

    2. Create a totals query that joins qryQualCheckFiltered to itself and selects those SubPKID's that match on at least 3 TestTypes. The SQL for this query looks like this:

    SELECT Q1.SubPKID, Q2.SubPKID
    FROM qryQualCheckFiltered AS Q1 INNER JOIN qryQualCheckFiltered AS Q2 ON
    Q1.DataValue2 = Q2.DataValue2 AND Q1.DataValue1 = Q2.DataValue1 AND Q1.TestType = Q2.TestType
    WHERE Q2.SubPKID>Q1.SubPKID
    GROUP BY Q1.SubPKID, Q2.SubPKID
    HAVING Count(*)>=3

    You may have to tweak this a bit, but the main poin is that you can use queries to find the required matches.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: finding if 3 out of 9 records match (A2k2,SP2,DAO)

    Hi Hans,
    Thank you.
    It almost works. The Count(*) is returning the count of the SubPKID's which have *any* matches in DataValue1 and DataValue2; i.e. if where SubPKID = 1 and where SubPKID = 3 and if they even have 1 match of any of the 9 TestTypes, Count(*) = 2. I need to pull the SubPKID's where 3 of the 9 tests within a certain SubPKID match the corresponding 3 of 9 tests within another SubPKID.
    Is this possible with queries?
    Thanks again.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: finding if 3 out of 9 records match (A2k2,SP2,DAO)

    Did you use the last bit from the SQL? The Having clause HAVING Count(*) > = 3 (without a space between > and =) selects only those SubPKID pairs that match on at least 3 TestTypes. If you want to find the pairs that match on exactly 3 TestTypes, use HAVING Count(*) = 3 instead.

  5. #5
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: finding if 3 out of 9 records match (A2k2,SP2,DAO)

    Hans,
    Yes. Attached in a text file you will find the code I am using.

    gdr
    Attached Files Attached Files
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  6. #6
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: finding if 3 out of 9 records match (A2k2,SP2,DAO)

    Hans,
    Also I noticed that it is not picking up the matches where there is an entry in DataValue1 and not an entry in DataValue2 (null or "").
    Can I use Nz in the Join expression? A query doesn't seem to like it (Nz used in a Join) though.
    Thanks.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: finding if 3 out of 9 records match (A2k2,SP2,DAO)

    Gary,

    1. The SQL you posted won't work, I think, because you include Q1.ProbeLocus and Q2.ProbeLocus without specifying any kind of grouping option for them.

    2. In the little test query I built, I get only the pairs that have at least 3 matches, as specified by Having Count(*) >=3

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: finding if 3 out of 9 records match (A2k2,SP2,DAO)

    You can use Nz in the join expression, but then you can't view the query in Design view any more, only in SQL view. The query will still work, though.

    SELECT Q1.SubPKID, Q2.SubPKID
    FROM tblQualCheck AS Q1 INNER JOIN tblQualCheck AS Q2 ON
    (Q1.TestType = Q2.TestType) AND (Nz(Q1.DataValue1,0) = Nz(Q2.DataValue1,0)) AND (Nz(Q1.DataValue2,0) =Nz( Q2.DataValue2,0))
    WHERE Q2.SubPKID>Q1.SubPKID
    GROUP BY Q1.SubPKID, Q2.SubPKID
    HAVING Count(*)>=3

  9. #9
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: finding if 3 out of 9 records match (A2k2,SP2,DAO)

    Hans,
    Sorry. That was a test that never got taken out. I have been runnig it without those in place.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  10. #10
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: finding if 3 out of 9 records match (A2k2,SP2,DAO)

    Hi Hans,
    Works slick. Nifty solution.
    It was the null values hiding the matches that "shoulda been" that was causing my Counts(*)'s to fall short.
    Thank you very much.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

Posting Permissions

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