Compare two recordsets with different numbers of records 1

[cjashwell]

Hi there,

I wonder if anyone could help me out with this problem:

I am trying to compare two recordsets with different numbers of records to see if there are any closely matched records in each. I need to know for each record in the first recordset whether there is a close match in the second recordset.

For the sake of simplicty I am only comparing the values in one field initially, although I may need to expand the routine to include two or perhaps three fields in the future.

The code I am working with so far is as follows:

Sub MatchForenames()

Dim strSQL1 As String
Dim strSQL2 As String
Dim num2000 As Integer, num2002 As Integer
Dim counter As Integer, counter2 As Integer

Dim wksCorin As DAO.Workspace
Dim dbsGPlus As DAO.Database
Dim rstForename2000 As DAO.Recordset
Dim rstForename2002 As DAO.Recordset
Dim fld As DAO.Field

Set wksCorin = DBEngine.Workspaces(0)
Set dbsGPlus = wksCorin.Databases(0)

strSQL1 = "SELECT [2000 GCSE Numbers].Forename FROM [2000 GCSE Numbers] ORDER BY [2000 GCSE Numbers].Forename"
Set rstForename2000 = dbsGPlus.OpenRecordset(strSQL1, dbOpenDynaset)
rstForename2000.MoveLast
'Debug.Print "2000 GCSE Numbers Record Count is : " & rstForename2000.RecordCount

strSQL2 = "SELECT [2002 Numbers].Forename FROM [2002 Numbers] ORDER BY [2002 Numbers].Forename"
Set rstForename2002 = dbsGPlus.OpenRecordset(strSQL2, dbOpenDynaset)
rstForename2002.MoveLast
'Debug.Print "2002 Numbers Record Count is : " & rstForename2002.RecordCount

num2000 = rstForename2000.RecordCount
num2002 = rstForename2002.RecordCount

rstForename2000.MoveFirst
rstForename2002.MoveFirst

'For each field in rst1 loop through every field in rst2 - print matches
For counter = 0 To num2000
For counter2 = 0 To num2002
For Each fld In rstForename2002
If fld.Value = rstForename2000.Fields(counter).Value Then
Debug.Print rstForename2000.Fields(counter).Value
End If
rstForename2002.MoveNext
Next fld
rstForename2000.MoveNext
Next counter
Set rstForename2000 = Nothing
Set rstForename2002 = Nothing
Set wksCorin = Nothing
Set dbsGPlus = Nothing

End Sub

Unfortunately, this doesn't seem to work, and I'm not sure why that is.

At present I am looking only for exact matches, but ultimately I want o be able to compare using a "*" or similar operator to return close matches. The forename field in the first rst has multiple forenames, and I will eventually need to match these to the single forenames in the second rst - then I can adjust the two so that they are reconciled.

I've tried searching this site but haven't yet found a clear enough explanation of how to compare two different recordsets, so if anyone can enlighten me I'd be most grateful.

Thanks in advance!

CJA

 

[gol4]

Not sure if any help but SQL using a inner join will produce any exact matches then you could also do the same using left and right and instr functions to look for similars also do a keyword search on soundx I'm sure Michael Red has done some excellent examples on this same subject in the past

 

[JeremyNYC]

Wow. Do you have any control over the way the data are stored? It's quite clear that the structure is completely un-normalized, and if that were fixed this would be a simple query operation.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[cjashwell]

Jeremy, thanks for that. I do have control over the data structures, although I'm working on a legacy system. Even if I normalise the 'forenames' fields it won't help my problem.

Example:

Table 1
Forename, Surname, DoB, ConcatField
Corin, Ashwell, 25/08/72,CorinAshwell25/08/72
Mia, Spencer, 30/06/74, MiaSpencer30/06/74
Saul, Parker, 01/05/78, SaulParker01/05/78

Table2
Forename, Surname, DoB, ConcatField
Corin Joe,Ashwell, 25/08/72, Corin JoeAshwell25/08/72
Mia Luce, Spencer, 30/06/74, Mia LuceSpencer30/06/74
Saul Richard, Parker, 01/05/78, Saul RichardParker01/05/78

I have concatenated the name fields with DoB to produce strings that I want to use to link tables. So I need to check for each record in table 2 if there is a near match in table 1, in order to identify the records with 'problem' entries.


Any suggestions?

CJA

 

[GummowN]

In table 2 add 2 more columns, sForename, sMiddlename (the S is for split - catchy hey!!!)

Run a query that takes everything before the space and puts it sForename, and everything after the space and puts it in sMiddlename.

Have a look in Visual basic help about the split function

Split(expression[, delimiter[, limit[, compare]]])

This will give you an idea how it works:

Public Function fncSplit(strName As String)
Dim i As Integer
Dim v As Variant
v = Split(strName)
For i = 0 To UBound(v)
MsgBox v(i)
Next i
End Function

You could manipulate this function to have the following:

Public Function fncForename(strName As String) as string
Dim i As Integer
Dim v As Variant
v = Split(strName)
fncForename = v(0)
End Function

Public Function fncMiddlename(strName As String) as string
Dim i As Integer
Dim v As Variant
v = Split(strName)
For i =1 to ubound(v)
fncMiddlename = fncMiddlename & v(i)
Next i
End Function

You could then use these in the query to update the new data columns, and then hopefully have some normalised data!!!

Let me know if that helps, otherwise I can always come up with something else

If at first you don't succeed, try

http://www.tek-tips.com

for the answer.

 

[cjashwell]

GummowN - thanks. That was a neat function, which has expedited my normalising of the tables in question.

Also, I wasn't aware of the Split function, so this was a useful post in that respect as well.

I can now use the reconciled Forename, Surname and DoB fields to link my tables, and then I will generate some proper unique key fields for my records to avoid this problem in future.

Thanks once again, and have a great day!

CJA