Sort and Flag records

  • Thread starter Thread starter Mikey B
  • Start date Start date
M

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
 
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
'====================================================
 
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
 
Back
Top