)K,
Lets try something else. Create a new query that looks something like:
qry_NormalAddress
SELECT ID, "Address Line 1" as AddField, [Address Line 1] as AddValue
FROM yourTable
WHERE [Address Line 1] IS NOT NULL
UNION ALL
SELECT ID, "Address Line 2" as AddField, [Address Line 2] as AddValue
FROM yourTable
WHERE [Address Line 2] IS NOT NULL
UNION ALL
....
SELECT ID, "Address Line 10" as AddField, [Address Line 10] as AddValue
FROM yourTable
WHERE [Address Line 10] IS NOT NULL
This query counts on you having and ID field in the table that you can use
to identify the "record" that the data came from. What this Union query
(you can only do this in the SQL view) does is normalizes your address data
into 3 fields (ID, AddField, and AddValue). Now, instead of having to write
10 queries to test each of the 10 fields, you should be able to write a
single query.
SELECT qNA.ID,
qNA.AddField,
qNA.AddValue,
C.CountryName,
InStr(qNA.AddValue, [C].[CountryName]) AS CntryPosn,
AC.AlternativeCountryName,
InStr(qNA.AddValue, [AC].[AlternativeCountryName]) AS
AltCntryPosn
FROM qry_NormalAddress as qNA,
tblCountryName C INNER JOIN tblAlternativeCountryName AC
ON C.CountryNameID = AC.CorrectCountryName
WHERE INSTR(qNA.AddValue, C.[CountryName]) > 0
OR INSTR(qNA.AddValue, AC.AlternateCountryName) > 0
This should identify the record (ID), the fieldname (AddField), and the
value from that field (AddValue) for each record where there is some sort of
match. You might also want to try changing the WHERE clause to:
WHERE qNA.AddValue Like "*" & C.[CountryName] & "*"
OR qNA.AddValue Like "*" & AC.AlternateCountryName & "*"
There is more than one way to skin a cat, and eventually, we will figure out
what it is in this case.
HTH
Dale
Kamitsukenu said:
Hi Dale,
I've tried it on column 5, and it works to a fashion; it will certain
countries, and only if the country is by itself in the field eg. it will
find
"USA" but not " NJ 01234, USA". Where it works, instr = 1 only.
The reason the data is so crudely named is because our customers send the
data in without any real format. I suppose a more accurate way of
explaining
the table would be:
Address Line 1
Address Line 2
Address Line 3
Address Line 4
Address Line 5
Address Line 6
Address Line 7
Address Line 8
Address Line 9
Address Line 10
The country can be in any one of these fields, and be in the middle of a
string.
What do you think?
Dale Fye said:
Have you tried it with one of the other fields. You say that there are
10
possible columns of data which need to be searched, well it obviously
isn't
[6]. Are you sure these fields don't have a name other than a numeric
value?
Can you change the field names to something more meaningful?
--
Email address is not valid.
Please reply to newsgroup only.
Kamitsukenu said:
Hi Dale,
Thanks for replying.
The ID key is indeed a number; the 'CorrectCountryName' field it joins
to is
a number as well, with a corresponding name attached to the
record(Alternative CountryName) - it never occured to me that I could
have
had the actual country name as a primary key (I assumed it had to be a
number).
The reason I have '6', is that there are 10 possible columns of data
which
needs to be searched for a country.
All the 'expr' values = 0. I think I have a problem...
:
You have created a Cartesian join between tblProcessData, and the
combination
of
all the Country/Alternate country names.
I'm a little surprised that you can join the ID field (usually
numeric) from
tblCountryName table to the [CorrectCountryName] field of the
AlternateCountryName table.
In order to identify the records where you actually have a match, you
need
to set the criteria for Exp1 to <> 0 or EXP2 to <> 0, something like:
WHERE INSTR(tblProcessData.[6], tblCountryName.[CountryName]) > 0
OR INSTR(tblProcessData.[6],
tblAlternateCountryName.AlternateCountryName) > 0
This will give you all the records where there is a match between
either the
country name or the alternate country name. I would include the
CountryName
and AlternateCountryName in the SELECT clause of the query as well.
To identify those that don't match one of the country or alternate
country
names, you will need to change the where clause to:
WHERE INSTR(...) = 0 AND INSTR(...) = 0
If all of your Expr1 and Expr2 values are actually 0 then you are
either
looking at the wrong field ([6] is not very descriptive) or you have
a much
larger problem.
HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.
:
Hi there,
I need alot of advice from you guys! Basically I have a table that
contains
address data, and I want to isolate the country name - it may be
either by
itself in a field or in a string.
To do this, I have created three tables:
one with supplied data containing address data (tblProcessData)
one with a list of countries (tblCountryName) and
one with a list of alternative country names
(tblAlternativeCountryName).
tblCountryName has all the 'correct' country names (eg. UK) and
tblAlternativeCountryName has any other spellings of this (eg.
United
Kingdom, Great Britain, GB etc. etc.) I have joined the two
together using
the Primary Key of tblCountryname to a number field in the
tblAlternativeCountryName (one-to-many relationship).
My question is, what is the best way of isolating the country name
in the
table? I was attempting to run an update query to find the country
name
based on 2222 records, but when I went to run it as a select query,
it comes
up with 142208 records, and the instr value is 0. Why is this?
SELECT tblProcessData.[6],
InStr([tblProcessData]![6],[tblCountryName]![CountryName]) AS
Expr1,
InStr([tblProcessData]![6],[tblAlternativeCountryName]![AlternativeCountryName])
AS Expr2
FROM tblProcessData, tblCountryName INNER JOIN
tblAlternativeCountryName ON
tblCountryName.CountryNameID =
tblAlternativeCountryName.CorrectCountryName;
Any help would be hugely appreciated!
