Searching multiple fields

[GeorgeAtkins]

Access 2003.
I import an Excel file into a temp table in order to append new records into
a set of related tables. Because there are no natural keys, I am attempting a
basic match of a combined first-middle-last name. If there is no exact match,
I assume the data is new and append it. The catch: the first,middle,last
names are in separate fields. I'm stuck (my normal state) trying to figure
out how to compare the data in both recordsets. That is, I'm not sure what
method to use that will let me search a table recordset across 3 fields at
one time.

Here is what I have. It works just great if I'm comparing only one name
against another single name, of course.

Set rsImp = db.OpenRecordset("Imported")
Set rsHug = db.OpenRecordset("tblHugMoms")
Set rsBirth = db.OpenRecordset("tblHugBirths")
Set rsBaby = db.OpenRecordset("tblHugBabies")
' get last name from first imported record.
With rsImp
.MoveFirst
Do Until .EOF
' MomLN = .Fields("MTHR_LST_NME") ' ORIGINAL VERSION.
' The FULL NAME I want to seach for...
ImpFullName = .Fields("MTHR_LST_NME") & .Fields("MTHR_FRST_NME")
& .Fields("MTHR_MIDD_NME")
With rsHug
' ********* HERE IS WHERE I AM STUCK.
.FindFirst "MotherLN = """ & MomLN & """"
If .NoMatch Then ' A NEW MOM. WE IMPORT HER DATA
ImpPK = rsImp.Fields(0)
Debug.Print "Adding " & ImpPK & " " & MomLN
.AddNew
.Fields("MotherFN") = rsImp.Fields("MTHR_FRST_NME")
.Fields("MotherMN") = rsImp.Fields("MTHR_MIDD_NME")
.Fields("MotherLN") = rsImp.Fields("MTHR_LST_NME")
.Fields("MailStreet") = rsImp.Fields("MTHR_MAIL_STRT")
.Fields("MailCity") = rsImp.Fields("MTHR_MAIL_CTY")
.Fields("MailState") = "MN" 'rsImp.Fields("MTHR_MAIL_ST")
.Fields("MailZip") = rsImp.Fields("MTHR_MAIL_ZIP")
.Update
' ...
loop
end with
---------------------------------------
So, how can I search a recordset for multiple values at one time? Or is
there a better method? If you need more info, let me know and I'll be happy
to oblige.
Thanks in advance!
george

 

[GeorgeAtkins]

One IMPORTANT POINT I FORGOT:
These are LINKED tables.

 

[John Nurick]

Another important point: can you control the indexes on the linked
tables? If so, set a unique index on the three fields (unless they're
already the primary key).

Then all you need do is run a simple append query to append records from
the temp table: the index will prevent records being appended that match
existing records.

If you can't index the linked tables, I'd use DCount() to look for an
existing record:

With rsImp
Do Until .Eof
If DCount(1, "tblHugMoms",
"MotherLN=""" & .Fields("MTHR_LST_NME") & """ AND " _
& "MotherFN=""" & .Fields("MTHR_FRST_NME") & """ AND " _
& "MotherMN=""" & .Fields("MTHR_MIDD_NME") & """) = 0 Then
'Append new record to tblHugMoms
...
End If
Loop
End With

 

[GeorgeAtkins]

Great idea! Of course, the table already has a primary key, though the name
fields are not part of the key. I can't change it, as there are dependent
tables related to it. However, I like the DCOUNT solution, too. I'll run that
through the ringer. Thanks again for the good ideas, John.
George

 

[John Nurick]

You don't need to change the primary key, just have a unique index on
all three name fields. (And you may well need to remove that index in a
later stage of your data import-and-cleaning process.)

 

[GeorgeAtkins]

Thanks, I'll give it a run.

You don't need to change the primary key, just have a unique index on
all three name fields. (And you may well need to remove that index in a
later stage of your data import-and-cleaning process.)

 

[GeorgeAtkins]

Thanks, I'll give it a run.

You don't need to change the primary key, just have a unique index on
all three name fields. (And you may well need to remove that index in a
later stage of your data import-and-cleaning process.)