Special Linking: Optimizations/Suggestions?

[Ken]

I am joining data from two different databases together. I need to
join two tables. The first table (tblA) holds an entity with an
associated dates. Each entity can have multiple rows, each with a
different date.

The second table (tblB) has the some of the same entities with
different dates. Each entity can have multiple rows with multiple
dates.

My task is to join each record in tblA with the record in tblB where
the entity is the same AND the absolute difference between the dates
is at a minimum (i.e. the record closest in time).

For example, joining tblA and tblB on xyz:

tblA: tblB:
Entity Date1 Entity Date2
xyz 01/01/2001 xyz 01/01/2000
xyz 01/01/2002 xyz 02/25/2001
xyz 05/01/2002

Would return 1 record for each record in tblA:
Enity Date1 Date2
xyz 01/01/2001 02/25/2001
xyz 01/01/2002 05/01/2002

Any suggestions on how to do this? I've been using an equijoin on
tblA and tblB, then using a function that opens up a recordset
filtered to the entity, loops through and finds the closest date to
Date1, and returns true for the record where the closest date is equal
to Date2 of the current record. Then I filter for records where my
function returns TRUE. It is slow and cumbersome. There must be a
better way.



Here's the code, in case you're interested. A call to it from inside
a query would look like:

IsClosestRecord ([tblA].[Date1],"Date2",[tblB].[Date2],[tblA].[Entity],"tblB"):



Function IsClosestRecord(dteTargetDate, strExamDateField As String,
dteExamDate As Date, strENTITYID, strRecordSource As String, Optional
strWHERE As String) As String
'For the current record containing a date, is it the closest in
time out of all the other records
'in another recordset with the same entityID?

Dim rst As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim dteClosestDate As Date
Dim intClosestDays As Integer
Dim strSQL As String
Dim r As String
Dim intDiff As Integer

intClosestDays = 10000
'Find the date of the record closest to the target date
Set rst = New ADODB.Recordset
Set cnn = Application.CurrentProject.Connection
strSQL = "SELECT EntityID, " & strExamDateField & " FROM " &
strRecordSource & " WHERE EntityID ='" & strENTITYID & "'" &
IIf(strWHERE <> "", " AND " & strWHERE, "") & ";"
rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
While Not rst.EOF
intDiff = Abs(DateDiff("d", dteTargetDate,
rst(strExamDateField)))
If intDiff < intClosestDays Then
dteClosestDate = rst(strExamDateField)
intClosestDays = intDiff
End If
rst.MoveNext
Wend

'Return true if the date in the foreign table is the same as the
closest date
If dteExamDate = dteClosestDate Then
r = "True"
Else
r = "False"
End If

IsClosestRecord = r

End Function

 

[chris]

Try...


Query1
Select
tblA.Entity,
tblA.Date1,
tblB.Date2,
Abs(tblA.date1-tblB.Date2) as DDiff
From
tblA
inner join tblB ON
tblA.Entity = tblB.Entity

This gives each entity with every combination for dates
plus the date diff

Query2
Select
Query1.Entity,
Query1.Date1,
Min(DDiff) as MinDiff
From
Query1
Group By
Query1.Entity,
Query1.Date1

this picks the min diff for each entity
and then to add in Date2

Query3
Select
Query2.Entity,
Query2.Date1,
Query1.Date2
From
Query2
inner join Query1 on
Query2.Entity = Query1.Entity
AND Query1.Date2 = Query1.Date1
AND Query1.DDiff = Query2.MinDiff

-----Original Message-----
I am joining data from two different databases together. I need to
join two tables. The first table (tblA) holds an entity with an
associated dates. Each entity can have multiple rows, each with a
different date.

The second table (tblB) has the some of the same entities with
different dates. Each entity can have multiple rows with multiple
dates.

My task is to join each record in tblA with the record in tblB where
the entity is the same AND the absolute difference between the dates
is at a minimum (i.e. the record closest in time).

For example, joining tblA and tblB on xyz:

tblA: tblB:
Entity Date1 Entity Date2
xyz 01/01/2001 xyz 01/01/2000
xyz 01/01/2002 xyz 02/25/2001
xyz 05/01/2002

Would return 1 record for each record in tblA:
Enity Date1 Date2
xyz 01/01/2001 02/25/2001
xyz 01/01/2002 05/01/2002

Any suggestions on how to do this? I've been using an equijoin on
tblA and tblB, then using a function that opens up a recordset
filtered to the entity, loops through and finds the closest date to
Date1, and returns true for the record where the closest date is equal
to Date2 of the current record. Then I filter for records where my
function returns TRUE. It is slow and cumbersome. There must be a
better way.



Here's the code, in case you're interested. A call to it from inside
a query would look like:

IsClosestRecord ([tblA].[Date1],"Date2",[tblB].[Date2], [tblA].[Entity],"tblB"):



Function IsClosestRecord(dteTargetDate, strExamDateField As String,
dteExamDate As Date, strENTITYID, strRecordSource As String, Optional
strWHERE As String) As String
'For the current record containing a date, is it the closest in
time out of all the other records
'in another recordset with the same entityID?

Dim rst As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim dteClosestDate As Date
Dim intClosestDays As Integer
Dim strSQL As String
Dim r As String
Dim intDiff As Integer

intClosestDays = 10000
'Find the date of the record closest to the target date
Set rst = New ADODB.Recordset
Set cnn = Application.CurrentProject.Connection
strSQL = "SELECT EntityID, " & strExamDateField & " FROM " &
strRecordSource & " WHERE EntityID ='" & strENTITYID & "'" &
IIf(strWHERE <> "", " AND " & strWHERE, "") & ";"
rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
While Not rst.EOF
intDiff = Abs(DateDiff("d", dteTargetDate,
rst(strExamDateField)))
If intDiff < intClosestDays Then
dteClosestDate = rst(strExamDateField)
intClosestDays = intDiff
End If
rst.MoveNext
Wend

'Return true if the date in the foreign table is the same as the
closest date
If dteExamDate = dteClosestDate Then
r = "True"
Else
r = "False"
End If

IsClosestRecord = r

End Function
.