Query to lookup from external wordlist for match

E

elementdesign

Hi there

I'm relatively new to access' more advanced features, like queries etc. and
am happy ot supply more information as required.

I have 'inherited' a database which basically lumps most of its information
in one field. This can contain associated people, places etc.

What we are looking to do is create columns in this database to take
information from this one field, but split into appropriate data fields

What this query needs to do is:
1. look through row by row, finding matches against a set list of words (like
place names for example)
2. where it finds a match, this needs to be put in an empty column in the
appropriate row

I have looked for a way of doing this but with no luck. Hope someone can help.


Many thanks
Giles
 
K

Klatuu

Sounds like this one field is a memo field. Based on your description it
qualifies as one of the worst database designs ever.
Sorry, but what you want to do is virtually impossible. You may find some
of your matches, but you will also miss a large percentage of them. You
would actually be better off to create the tables and forms you need in a
proper format and have data entry people enter the values manually.

First, a query would not do it. It would take some very advanced VBA, but
still would not get you the results you want.
 
J

John Spencer

It can be done, but it probably won't be all the accurate. If the BigField is
a text field (255 characters maximum) you could use something like the
following.

Assumptions:
TheBigField is a text field (not a memo field)
A table of City Names exists

SELECT B.PrimaryKey, L.CityName
FROM TheBigTable As B INNER JOIN ListOfCities as L
ON B.TheBigField LIKE "*" & L.CityName & "*"

You could turn that into an update query
UPDATE TheBigTable As B INNER JOIN ListOfCities as L
ON B.TheBigField LIKE "*" & L.CityName & "*"
SET B.CityName = [L].[CityName]


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

Jeff Boyce

Basic database design "rules" call for "one fact/one field". Having
everything lumped into one big field will make finding anything quite
difficult for the relationally-oriented features/functions in Access.

It all starts with the data. Can you describe the data "entities" and
"relationships"? More specific description may lead to more specific
suggestions.

Can you provide an example of some of your data?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Top