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

1. ## 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

2. ## 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. ## 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

4. ## 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. ## 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

6. ## 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

7. ## 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. ## 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. ## 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

10. ## 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

#### Posting Permissions

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