Query for partial matches

[Todd]

Will the below work...first is a short desription of the
problem, and then a possible solution. It comes from
another newsgroup.....

Thank you.....

I think I understand the situation now. You've got a
field in one table that contains values like
this: (and is 14000 records).

1 milk eggs salt dripping
2 butter eggs milk salt pepper
3 beef onions garlic wine stock carrots mushrooms
bacon butter
4 lamb barley potatoes onions dripping
5 beef eggs wheat garlic oil
6 eggplant zucchini onion tomatoes
7 beef potatoes

and so on,
and a second table with a field like this (2000 records)
beef
potatoes
lamb
garlic
and you want to return every record that includes any of
these items.

I don't know any way of doing this in straight SQL. The
problem is the non-normalised field.

The best approach IMO would be to restructure the data,
replacing this
non-normalised field with records (one per parent or
ancestor) in a
related table.

If this isn't possible, I fear you'll need to use VBA
code that creates
a recordset on the second table and then iterates through
the records.
With fewer records you could use this to build up a long
SQL WHERE
clause, but with 2000 you'd probably run up against
Access's limits on
length and or complexity of SQL statements. So I expect
you'd need to
run a query 2000 times and use a temporary table to store
the primary
keys of the selected records. Something like this air
code:

Dim rsR As DAO.Recordset
Dim dbD as DAO.Database

Set dbD = CurrentDB()
Set rsR = dbD.OpenRecordset("TableTwo")
dbd.Execute "DELETE FROM TempTable;"
With rsR
Do Until .EOF
dbD.Execute "INSERT INTO TempTable (AnimalID) " _
& "SELECT AnimalID FROM TableOne WHERE " _
& "Pedigree LIKE """ & .Fields("AnimalID") _
& """;"
.MoveNext
Loop
End With
rsR.Close
Set rsR = Nothing
Set dbD = Nothing

But I'm not a SQL wizard. It may be worth posting in the
microsoft.public.access.queries newsgroup where the real
experts hang
out.

 

[John Spencer (MVP)]

You would be better off redesigning the table structure as you were told. You
can write a query with a join to do this. You CANNOT do this in the query grid,
but must do it in SQL view. Of course, I must say, that I am guessing on what
you want to do as that is not clear from what you posted here.

SELECT RecordID, IngredientList
FROM Table1 INNER JOIN Table2

 

[Todd]

Hi John,
Sorry. I'll try again. I need to
somehow, get a text field from one table to be queried,
that includes any part of a record (that's in another
table).

I have one table that has a text field of 155 char. This
table is 14000 records.

I have a second table that has another text field, where
the char length is 7 char. It has about 2000 records.

I need to figure out how to query table one (14000) that
include any part of the text records in the second table.

I know I can do them one at a time. But I don't want to
do 2000 like that.

It's basically pedigree type data. Where the two parents
are listed in one text field that is very messy (table
one above). I have a list of mom and dads (second
table). That are included in this messy pedigree field.
I'm just trying to find where the mom and dads are in the
parentage of table one.

Let me know if you have any other ideas. Maybe I
explained myself better this time. Thanks very much for
your input and help!

Best regards,
Todd

 

[spencer4]

Then you should be able to do it as I explained.

Another method would be to have BOTH tables in one query without a join and
then use criteria against the text field of 155 characters. In the grid, that
would look something like:

Field: Text155
Table: TableWith14000records
Criteria: Like "*" & [Table2000].[Text7Chars] & "*"

This may or may not be an updatable query. But that is a different problem.

 

[Todd]

Thanks for your help John.

Kind regards,
Todd

-----Original Message-----
Then you should be able to do it as I explained.

Another method would be to have BOTH tables in one query without a join and
then use criteria against the text field of 155 characters. In the grid, that
would look something like:

Field: Text155
Table: TableWith14000records
Criteria: Like "*" & [Table2000].[Text7Chars] & "*"

This may or may not be an updatable query. But that is a different problem.

Hi John,
Sorry. I'll try again. I need to
somehow, get a text field from one table to be queried,
that includes any part of a record (that's in another
table).

I have one table that has a text field of 155 char. This
table is 14000 records.

I have a second table that has another text field, where
the char length is 7 char. It has about 2000 records.

I need to figure out how to query table one (14000) that
include any part of the text records in the second table.

I know I can do them one at a time. But I don't want to
do 2000 like that.

It's basically pedigree type data. Where the two parents
are listed in one text field that is very messy (table
one above). I have a list of mom and dads (second
table). That are included in this messy pedigree field.
I'm just trying to find where the mom and dads are in the
parentage of table one.

Let me know if you have any other ideas. Maybe I
explained myself better this time. Thanks very much for
your input and help!

Best regards,
Todd



as you were told. You

this in the query grid,

I am guessing on what

.