Queries - a baffling question

[Cool Dude]

Hi,

I've got two fields with contents as below:

Field1 Field2
------------- ----------------
Apple Apple Orchard
Pear Grove of Bananas
Banana Peach Tree
Grape Cherry Pits


What I want to do is to create a query that somehow finds the contents of
the first field in
the values of the second field... therefore returning (from above)

Apple Orchard (matches with Apple)
Grove of Bananas (matches with Banana)

Is this possible?

And if so, how do I do it?

I've tried various permutations on Like "*" &

![Field1] & "*" but
that doesn't seem to work.

There's probably something easy... I just can't seem to find the right
combination to put into the Query Builder.

Can anyone help?

Thanks in advance.

 

[arthurjr07]

You need to create a dummy table with same column with the table

Then create query that will copy all the records in that table to the
dummy table.

after that you can now use the query below.

SELECT Distinct table.field2
FROM table INNER JOIN dummytable ON table.field2 like "*" &
dummytable.field1 & "*" ;

 

[Cool Dude]

Yeah, I wish. Back to school... yay!! Someone ELSE paying the bills!!
Hurrah!!

Nope, sorry.. It's a database that I'm developing for work.

I've (obviously) sanitized the data to avoid company confidentials...

This sounds like a homework assignment... ??

--
Rob Mastrostefano

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

Hi,

I've got two fields with contents as below:

Field1 Field2
------------- ----------------
Apple Apple Orchard
Pear Grove of Bananas
Banana Peach Tree
Grape Cherry Pits


What I want to do is to create a query that somehow finds the contents of
the first field in
the values of the second field... therefore returning (from above)

Apple Orchard (matches with Apple)
Grove of Bananas (matches with Banana)

Is this possible?

And if so, how do I do it?

I've tried various permutations on Like "*" &

![Field1] & "*" but
that doesn't seem to work.

There's probably something easy... I just can't seem to find the right
combination to put into the Query Builder.

Can anyone help?

Thanks in advance.

 

[DEBBRA ANDERSON]

Sounds to me like you need to go to microsoft.com and download some sample
databases, they have several really helpful downloads. I've learned tons
from them.

Check these out:
http://office.microsoft.com/en-us/officeupdate/CD011124291033.aspx

 

[Dirk Goldgar]

Hi,

I've got two fields with contents as below:

Field1 Field2
------------- ----------------
Apple Apple Orchard
Pear Grove of Bananas
Banana Peach Tree
Grape Cherry Pits


What I want to do is to create a query that somehow finds the
contents of the first field in
the values of the second field... therefore returning (from above)

Apple Orchard (matches with Apple)
Grove of Bananas (matches with Banana)

Is this possible?

And if so, how do I do it?

I've tried various permutations on Like "*" &

![Field1] & "*"
but that doesn't seem to work.

There's probably something easy... I just can't seem to find the right
combination to put into the Query Builder.

Can anyone help?

Thanks in advance.

Is this what you're after?

SELECT B.Field2
FROM Table1 AS A INNER JOIN Table1 AS B
ON B.Field2 Like "*" & A.Field1 & "*";

 

[Cool Dude]

Thanks, but that gives me an error message of "Syntax Error in FROM clause"

and as it turns out, table is a reserved word in SQL - so it works as long
as I change the name of the table "table" to "table1"

COOL!!!

Time to go to sleep... hey, what a deal, 2 hours before I have to be at work
again!! ARGH!!

Thanks Arthur.. 2 hours is better than NO hours!

 

[Dirk Goldgar]

You need to create a dummy table with same column with the table

Then create query that will copy all the records in that table to the
dummy table.

after that you can now use the query below.

SELECT Distinct table.field2
FROM table INNER JOIN dummytable ON table.field2 like "*" &
dummytable.field1 & "*" ;

Note that there's no need to create a second, duplicate table. You can
use the same table twice in a query, with different aliases. See my
other reply in this thread for an example.

 

[Cool Dude]

HI Debra,

Thanks for the suggestion.. but I've already been there. Unless I missed
something,
there wasn't anything on "Inner joins for dummies"

I can do parameter queries and outer joins no problem... but for some reason
inner joins are always baffling.

Anyone know of a good site that IS "inner joins for dummies"?

Thanks!

Sounds to me like you need to go to microsoft.com and download some sample
databases, they have several really helpful downloads. I've learned tons
from them.

Check these out:
http://office.microsoft.com/en-us/officeupdate/CD011124291033.aspx


--
DEBBRA ANDERSON

Hi,

I've got two fields with contents as below:

Field1 Field2
------------- ----------------
Apple Apple Orchard
Pear Grove of Bananas
Banana Peach Tree
Grape Cherry Pits


What I want to do is to create a query that somehow finds the contents of
the first field in
the values of the second field... therefore returning (from above)

Apple Orchard (matches with Apple)
Grove of Bananas (matches with Banana)

Is this possible?

And if so, how do I do it?

I've tried various permutations on Like "*" &

![Field1] & "*" but
that doesn't seem to work.

There's probably something easy... I just can't seem to find the right
combination to put into the Query Builder.

Can anyone help?

Thanks in advance.

 

[Douglas J. Steele]

In actual fact, you can't use the query builder to do what you're trying to
do.

Forget that you're trying to do a Like, and just build a normal query as
though field1 equals field2.

Then, go into the SQL for your query (choose SQL View from the View menu)

You'll have something like:

SELECT Table1.Field1, Table1.Field2, Table2.Field1, Table2.Field2
FROM Table1 INNER JOIN Table2
ON Table1.Id = Table2.Id

Change the ON condition to:

ON Table1.Id Like "*" & Table2.Id & "*"

(FWIW, what you're trying to do isn't an inner join. It's usually referred
to as a frustrated join)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

HI Debra,

Thanks for the suggestion.. but I've already been there. Unless I missed
something,
there wasn't anything on "Inner joins for dummies"

I can do parameter queries and outer joins no problem... but for some
reason
inner joins are always baffling.

Anyone know of a good site that IS "inner joins for dummies"?

Thanks!

Sounds to me like you need to go to microsoft.com and download some
sample databases, they have several really helpful downloads. I've
learned tons from them.

Check these out:
http://office.microsoft.com/en-us/officeupdate/CD011124291033.aspx


--
DEBBRA ANDERSON

Hi,

I've got two fields with contents as below:

Field1 Field2
------------- ----------------
Apple Apple Orchard
Pear Grove of Bananas
Banana Peach Tree
Grape Cherry Pits


What I want to do is to create a query that somehow finds the contents
of the first field in
the values of the second field... therefore returning (from above)

Apple Orchard (matches with Apple)
Grove of Bananas (matches with Banana)

Is this possible?

And if so, how do I do it?

I've tried various permutations on Like "*" &

![Field1] & "*"
but that doesn't seem to work.

There's probably something easy... I just can't seem to find the right
combination to put into the Query Builder.

Can anyone help?

Thanks in advance.