I agree with micron, you have three rows in the first dataset that are identical which match 2 identical rows in the second table. In essence if you joined these two tables on the three fields you're interested in you'd get 6 records for ID 1, Date 1/1/2011, Time 1. It seems to me you are more likely need some sort of algorithm to determine if each ID/Date/Time has a match but only match the item 1 time otherwise look for a new match. This would likely involve some sort of table update as well I can't think of a clean way to do it in queries. It also matters whether or not you want to find records in table 2 that have no match in table 1.
This was my table1
T1_ID |
T1_Date |
T1_Hour |
MatchNum |
1 |
1/1/2011 |
1 |
|
1 |
1/1/2011 |
2 |
|
1 |
1/1/2011 |
1 |
|
1 |
1/1/2011 |
1 |
|
1 |
1/1/2011 |
3 |
|
1 |
1/1/2011 |
3 |
|
1 |
1/1/2011 |
3 |
|
1 |
1/1/2011 |
3 |
|
This was my table2
T2_ID |
T2_Date |
T2_Hour |
MatchNum |
1 |
1/1/2011 |
3 |
|
1 |
1/1/2011 |
3 |
|
1 |
1/1/2011 |
1 |
|
1 |
1/1/2011 |
1 |
|
1 |
1/1/2011 |
3 |
|
1 |
1/1/2011 |
3 |
|
1 |
1/1/2011 |
3 |
|
1 |
1/1/2011 |
3 |
|
This is the query I used to find unique combinations of ID, date and hour from table1
Code:
SELECT Table1.T1_ID, Table1.T1_Date, Table1.T1_HourFROM Table1
GROUP BY Table1.T1_ID, Table1.T1_Date, Table1.T1_Hour
ORDER BY Table1.T1_ID, Table1.T1_Date, Table1.T1_Hour;
This is the code I ran:
Code:
Dim rstT1 As Recordset
Dim rstUpdate As Recordset
Dim iID, iHour As Long
Dim dDate As Date
Dim iT1, iT2 As Long
Dim iMatchNum As Long
Dim iMatchID As Long
Dim i As Long
CurrentDb.Execute ("UPDATE Table1 SET MatchNum = null")
CurrentDb.Execute ("UPDATE Table2 SET MatchNum = null")
Set rstT1 = CurrentDb.OpenRecordset("qryTable1Processing")
Do While rstT1.EOF <> True
iID = rstT1!t1_id
dDate = rstT1!t1_date
iHour = rstT1!t1_hour
iT1 = DCount("*", "Table1", "[T1_ID] = " & iID & " AND [T1_Date] = #" & dDate & "# AND [T1_Hour] = " & iHour)
iT2 = DCount("*", "Table2", "[T2_ID] = " & iID & " AND [T2_Date] = #" & dDate & "# AND [T2_Hour] = " & iHour)
If iT1 > 0 And iT2 > 0 Then
If iT1 > iT2 Then
iMatchNum = iT2
Else
iMatchNum = iT1
End If
Debug.Print dDate & " " & iMatchNum
Set rstUpdate = CurrentDb.OpenRecordset("SELECT TOP " & iMatchNum & " MatchNum FROM Table1 WHERE [T1_ID] = " & iID & " AND [T1_Date] = #" & dDate & "# AND [T1_Hour] = " & iHour)
i = 0
Do While rstUpdate.EOF <> True
i = i + 1
rstUpdate.Edit
rstUpdate!matchnum = iMatchID + i
rstUpdate.Update
rstUpdate.MoveNext
Loop
rstUpdate.Close
Set rstUpdate = CurrentDb.OpenRecordset("SELECT TOP " & iMatchNum & " MatchNum FROM Table2 WHERE [T2_ID] = " & iID & " AND [T2_Date] = #" & dDate & "# AND [T2_Hour] = " & iHour)
i = 0
Do While rstUpdate.EOF <> True
i = i + 1
rstUpdate.Edit
rstUpdate!matchnum = iMatchID + i
rstUpdate.Update
rstUpdate.MoveNext
Loop
rstUpdate.Close
Debug.Print "adding " & iMatchID & " to " & iMatchNum
iMatchID = iMatchID + iMatchNum
End If
rstT1.MoveNext
Loop
rstT1.Close
Set rstT1 = Nothing
End Sub
After it was done running you could select all records from table 1 or table 2 to find ones that didn't have a match but testing for a null value in the matchnum field.