How to extract records matching a list of strings?

[T Magritte]

I've decided to take the plunge and learn how to use Access 2007. I've
set up a database but am having difficulty with one task.

I have a master table with a unique integer ID and a text name which
is usually unique but technically may not be.

I have a separate list of names. I want to know which names in the
second list are or are not present in the master table, and for those
that are present, what are the unique IDs?

The second list of strings may match exactly the string in the master
table, or may match exactly but have a different case, or may be a
subset of the string in the master table. I'd like to find all 3
cases. Where the match is a sub-string, I'd like to find all strings
in the master table that contain that sub-string.

My first attempt was just to create a new table with a single field
being a text field, and each record holding one of the strings in the
second list. I then created a query containing the ID and name in the
master table, and the string in the second table, and creating a join
between the string in the second table and the string in the master
table, with an arrow pointing towards the master table.

And the result: no matches.

I then modified a few of the strings in the second table so that they
matched exactly (including case) some record in the master table. But
still no matches.

So I'm at a loss as to how to get the result I'm looking for. Am I
going about this completely the wrong way or is there some little
thing I'm not doing?

Thanks for your help!

 

[KARL DEWEY]

This will get you part way then just use UNMATCH query --
ID LName
1 Bill
2 Sam
3 Joe
4 Tom
5 Bob

ListOfNames
ID FullName
1 Sam Jones
2 Bill Rex
3 Joe Bob Brewer
4 Ted Nan


SELECT ListOfNames.ID, ListOfNames.FullName, MasterList.ID, MasterList.LName
FROM ListOfNames, MasterList
WHERE (((ListOfNames.FullName) Like "*" & [MasterList].[LName] & "*"));

ListOfNames.ID FullName MasterList.ID LName
2 Bill Rex 1 Bill
1 Sam Jones 2 Sam
3 Joe Bob Brewer 3 Joe
3 Joe Bob Brewer 5 Bob

 

[Marshall Barton]

I've decided to take the plunge and learn how to use Access 2007. I've
set up a database but am having difficulty with one task.

I have a master table with a unique integer ID and a text name which
is usually unique but technically may not be.

I have a separate list of names. I want to know which names in the
second list are or are not present in the master table, and for those
that are present, what are the unique IDs?

The second list of strings may match exactly the string in the master
table, or may match exactly but have a different case, or may be a
subset of the string in the master table. I'd like to find all 3
cases. Where the match is a sub-string, I'd like to find all strings
in the master table that contain that sub-string.

My first attempt was just to create a new table with a single field
being a text field, and each record holding one of the strings in the
second list. I then created a query containing the ID and name in the
master table, and the string in the second table, and creating a join
between the string in the second table and the string in the master
table, with an arrow pointing towards the master table.

And the result: no matches.

I then modified a few of the strings in the second table so that they
matched exactly (including case) some record in the master table. But
still no matches.

I have done that kind of thing several times by using a
non-equi join (SQL view only). E.g.

SELECT t1.ID, t1.[name field], t2.[partial name]
FROM [master table[ As t1 INNER JOIN [second list] As t2
ON t1.field Like "*" & t2.[partial name] & "*"

Note that Access is case insensitive by default so if that's
a problem, it's because you did something(??) to force case
sensitive comparisons.

 

[T Magritte]

Thanks for the reply guys.

I haven't gotten as far as learning SQL commands yet and was hoping I
could do most of what I need with the visual interface for now, but am
willing to learn as necessary.

I did notice in Karl's response that there is an ID column in each
table. The list of names I'm trying to query against is a table with a
single column containing the names. I'm wondering if this is the
problem. Do all tables need a unique ID column to act as a key? I was
thinking since this table is only used to hold values I'm querying
against in another table I wouldn't need a key. Is that incorrect?

Thanks!

 

[Marshall Barton]

I haven't gotten as far as learning SQL commands yet and was hoping I
could do most of what I need with the visual interface for now, but am
willing to learn as necessary.

I did notice in Karl's response that there is an ID column in each
table. The list of names I'm trying to query against is a table with a
single column containing the names. I'm wondering if this is the
problem. Do all tables need a unique ID column to act as a key? I was
thinking since this table is only used to hold values I'm querying
against in another table I wouldn't need a key. Is that incorrect?

A Primary Key field is not absolutely required in some
simple situations, BUT it is strongly recommended in all
tables. There is no requirement to use a surrogate key such
as an AutoNumber, however the field really has to be unique
and making it the primary key will guarantee that.

OTOH, there are innumerable cases where two people have the
same name so more information is usually necessary and many
folks think an autonumber surrogate primary key is an
adequate alternative.

 

[T Magritte]

So it seems like the initial failure was due to extra whitespace in
the string fields. I did an update using the trim function and then
the join worked as expected.

I tried the approaches you guys suggested and they do seem to work for
matching substring as opposed to full string. However, they won't show
the test strings that don't produce a match, at least not without
doing a separate unmatch query as suggested.

However, I did manage to get both substring matches and unmatched
strings using this query:

SELECT find.[partial name], master.[full name], master.[unique id]
FROM [second list] AS find LEFT JOIN [master list] AS master ON
master.[full name] like "*" & find.[partial name] & "*";

The only issue is it can't be displayed in the build view. Is there
any way to build such a query using the build view or do you need to
use the SQL view?

Thanks again!

I have done that kind of thing several times by using a
non-equi join (SQL view only).  E.g.

SELECT t1.ID, t1.[name field], t2.[partial name]
        FROM [master table[ As t1 INNER JOIN [second list] As t2
                ON t1.field Like "*" & t2.[partial name] & "*"

Note that Access is case insensitive by default so if that's
a problem, it's because you did something(??) to force case
sensitive comparisons.

 

[John Spencer]

No, this type of query cannot be built using the query design view
(query grid). The query grid can only show equi-joins (fields are equal).


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[T Magritte]

Okay.

Thanks for all your help!