Sort and Flag records

[Mikey B]

This is a design question.
I have a table tblMainRecords that has 50k records in it.
I also have a table tblFlagList that has around 100 records in it.

In tblMainRecords there is a strDescription field.
In tblFlagList there is a field strItemName.

I need to look through each record of tblMainRecords' strDescription field
for anything on the strItemName list and flag another field(strFlg) on
tblMainRecords if it finds a match.

My question is, what would be the best way to do this sort/update?
I was thinking either a complicated Update query or a complicated recordest
loop.
Maybe a Dlookup in a recordset search?

I will work out the code, if someone can tell what might be the tack to take.
Thanks for any input,
Mike

 

[John Spencer]

Generally an update query will be faster and introduce less bloat.
However, there are times when looping through a recordset is more
efficient. The following MIGHT work or you might get an error.

Two queries:
'Clear the flag in all records
UPDATE tblMainRecords
Set StrFlag = False

'Set the flag in appropriate records

UPDATE tblMainRecords
SET tblMainRecords.strFlag = True
WHERE tblMainRecords.PrimaryKey in
(SELECT tblMainRecords.PrimaryKey
FROM tblMainRecords INNER JOIN tblFlagList
ON tblMainRecords.Description LIKE "*" & tblFlagList.strItemName & "*")

Of course, you don't need to use LIKE and the wildcards if Description
is equal to strItemName, but if description contains strItemName as part
of the description then you do.

Normally, I would recommend that you calculate strFlag value on the fly
in your query. It will be more reliable that way. If your data is
stable then updating could possibly be a better solution if you need
speed of operation over accuracy of data. Anytime you add a record to
tblMainrecords or edit a description, you would need to ensure that you
calculated strFlag. If you change the list of items, then you need to
ensure you recalculate strFlag for the entire tblMainRecords table.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Mikey B]

It did not like my Description field as a memo field. -> Can't Union a Memo.

Once I set it as a text field it worked like a champ.

Many Thanks Mr. Spencer,

Mike