Using a Non-Equi Join select records w search term Begin, Middle,

P

Patrick Briggs

Hi,

This is a variation of an earlier question I asked.

I'm using Access 2003 on a Windows XP box.

I've got one table

(Donor_Lenghts_tbl)

of individuals donors and organization donors. I don't have a donor type
field to work off of. I do have the LastName column though - it's has
legitimate last names for the Individuals and then full organization names
for Organizations.

I have another table of one column with a list of search terms

(Org_Criteria_tbl)

populated with about 200 terms (church, temple, club etc.)

I know I can use a Non-equi join to do a query that identifies the
Organization donors within the Donor_Lengths_tbl:

SELECT *
FROM Donor_Lengths_tbl AS D
INNER JOIN Org_Criteria_tbl AS O ON D.Ln Like "* " & O.Org_Criteria & " *"

This successfully finds any Organization donors in the Donor_Lengths_tbl
which are in the middle of the field Ln.

I'd like to also be able to also get the Organization donors in which the
search term falls at the beginning or at the end of Ln field.

This didn't work for me:

SELECT *
FROM Donor_Lengths_tbl AS D
INNER JOIN Org_Criteria_tbl AS O ON D.Ln Like "* " & O.Org_Criteria & " *"
OR INNER JOIN Org_Criteria_tbl AS O ON D.Ln Like O.Org_Critieria & " *"
OR INNER JOIN Org_Criteria_tbl AS O ON D.Ln Like "* " & O.Org_Criteria;

Other than running three separate queries using each of the above types of
non-equi joins, is there another option?

Google searching for "multi-part non-equi join" and looking up "non-equi
join" posts in this forum weren't fruitful so I thought I'd ask.

Thanks in advance,

Patrick Briggs,
Pasadena, CA
 
J

Jeff L

In your original query you have:
Like "* " & O.Org_Criteria & " *"

If you take out the spaces, it should give you what you are looking
for.
Like "*" & O.Org_Criteria & "*"

Hope that helps!
 
P

Patrick Briggs

Jeff,

Thanks for the suggestion.

The only problem with doing that is that sometimes I'll pick up individuals
when the Organization criteria is in their last name (i.e. Templeton,
Churchill etc).

By breaking it out by where the Organization criteria appears in the field
(beginning, middle or end), I hope to avoid that.

Regards,

Patrick
 
G

giorgio rancati

Hi Patrick,

try this
----
SELECT *
FROM Donor_Lengths_tbl AS D
INNER JOIN Org_Criteria_tbl AS O
ON D.Ln Like "* " & O.Org_Criteria & " *"
OR D.Ln Like O.Org_Criteria & " *"
OR D.Ln Like "* " & O.Org_Criteria;
 
P

Patrick Briggs

OUTSTANDING!! If I had played around with it a little more....(sigh).

Thank you!! Have you done these kind of non-equi joins often?

Regards,

Patrick

giorgio rancati said:
Hi Patrick,

try this
----
SELECT *
FROM Donor_Lengths_tbl AS D
INNER JOIN Org_Criteria_tbl AS O
ON D.Ln Like "* " & O.Org_Criteria & " *"
OR D.Ln Like O.Org_Criteria & " *"
OR D.Ln Like "* " & O.Org_Criteria;
----

bye
--
Giorgio Rancati
[Office Access MVP]

Patrick Briggs said:
Hi,

This is a variation of an earlier question I asked.

I'm using Access 2003 on a Windows XP box.

I've got one table

(Donor_Lenghts_tbl)

of individuals donors and organization donors. I don't have a donor type
field to work off of. I do have the LastName column though - it's has
legitimate last names for the Individuals and then full organization names
for Organizations.

I have another table of one column with a list of search terms

(Org_Criteria_tbl)

populated with about 200 terms (church, temple, club etc.)

I know I can use a Non-equi join to do a query that identifies the
Organization donors within the Donor_Lengths_tbl:

SELECT *
FROM Donor_Lengths_tbl AS D
INNER JOIN Org_Criteria_tbl AS O ON D.Ln Like "* " & O.Org_Criteria & " *"

This successfully finds any Organization donors in the Donor_Lengths_tbl
which are in the middle of the field Ln.

I'd like to also be able to also get the Organization donors in which the
search term falls at the beginning or at the end of Ln field.

This didn't work for me:

SELECT *
FROM Donor_Lengths_tbl AS D
INNER JOIN Org_Criteria_tbl AS O ON D.Ln Like "* " & O.Org_Criteria & " *"
OR INNER JOIN Org_Criteria_tbl AS O ON D.Ln Like O.Org_Critieria & " *"
OR INNER JOIN Org_Criteria_tbl AS O ON D.Ln Like "* " & O.Org_Criteria;

Other than running three separate queries using each of the above types of
non-equi joins, is there another option?

Google searching for "multi-part non-equi join" and looking up "non-equi
join" posts in this forum weren't fruitful so I thought I'd ask.

Thanks in advance,

Patrick Briggs,
Pasadena, CA
Click to expand...
Click to expand...
 
G

giorgio rancati

no, but Sql language is my passion
:)
--
Giorgio Rancati
[Office Access MVP]

Patrick Briggs said:
OUTSTANDING!! If I had played around with it a little more....(sigh).

Thank you!! Have you done these kind of non-equi joins often?

Regards,

Patrick

giorgio rancati said:
Hi Patrick,

try this
----
SELECT *
FROM Donor_Lengths_tbl AS D
INNER JOIN Org_Criteria_tbl AS O
ON D.Ln Like "* " & O.Org_Criteria & " *"
OR D.Ln Like O.Org_Criteria & " *"
OR D.Ln Like "* " & O.Org_Criteria;
----

bye
--
Giorgio Rancati
[Office Access MVP]

"Patrick Briggs" <[email protected]> ha scritto nel
messaggio news:[email protected]...
Hi,

This is a variation of an earlier question I asked.

I'm using Access 2003 on a Windows XP box.

I've got one table

(Donor_Lenghts_tbl)

of individuals donors and organization donors. I don't have a donor type
field to work off of. I do have the LastName column though - it's has
legitimate last names for the Individuals and then full organization names
for Organizations.

I have another table of one column with a list of search terms

(Org_Criteria_tbl)

populated with about 200 terms (church, temple, club etc.)

I know I can use a Non-equi join to do a query that identifies the
Organization donors within the Donor_Lengths_tbl:

SELECT *
FROM Donor_Lengths_tbl AS D
INNER JOIN Org_Criteria_tbl AS O ON D.Ln Like "* " & O.Org_Criteria &
Click to expand...
"
*"
This successfully finds any Organization donors in the Donor_Lengths_tbl
which are in the middle of the field Ln.

I'd like to also be able to also get the Organization donors in which the
search term falls at the beginning or at the end of Ln field.

This didn't work for me:

SELECT *
FROM Donor_Lengths_tbl AS D
INNER JOIN Org_Criteria_tbl AS O ON D.Ln Like "* " & O.Org_Criteria &
Click to expand...
"
*"
OR INNER JOIN Org_Criteria_tbl AS O ON D.Ln Like O.Org_Critieria & " *"
OR INNER JOIN Org_Criteria_tbl AS O ON D.Ln Like "* " & O.Org_Criteria;

Other than running three separate queries using each of the above types of
non-equi joins, is there another option?

Google searching for "multi-part non-equi join" and looking up "non-equi
join" posts in this forum weren't fruitful so I thought I'd ask.

Thanks in advance,

Patrick Briggs,
Pasadena, CA
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Actually, you could do this all in one, by concatenating spaces to the
beginning and end of D.LN in the On clause. I think it should make no
difference in performance.

SELECT *
FROM Donor_Lengths_tbl AS D
INNER JOIN Org_Criteria_tbl AS O
ON " " & D.Ln & " " Like "* " & O.Org_Criteria & " *"

Patrick Briggs said:
OUTSTANDING!! If I had played around with it a little more....(sigh).

Thank you!! Have you done these kind of non-equi joins often?

Regards,

Patrick

giorgio rancati said:
Hi Patrick,

try this
----
SELECT *
FROM Donor_Lengths_tbl AS D
INNER JOIN Org_Criteria_tbl AS O
ON D.Ln Like "* " & O.Org_Criteria & " *"
OR D.Ln Like O.Org_Criteria & " *"
OR D.Ln Like "* " & O.Org_Criteria;
----

bye
--
Giorgio Rancati
[Office Access MVP]

Patrick Briggs said:
Hi,

This is a variation of an earlier question I asked.

I'm using Access 2003 on a Windows XP box.

I've got one table

(Donor_Lenghts_tbl)

of individuals donors and organization donors. I don't have a donor
type
field to work off of. I do have the LastName column though - it's has
legitimate last names for the Individuals and then full organization
names
for Organizations.

I have another table of one column with a list of search terms

(Org_Criteria_tbl)

populated with about 200 terms (church, temple, club etc.)

I know I can use a Non-equi join to do a query that identifies the
Organization donors within the Donor_Lengths_tbl:

SELECT *
FROM Donor_Lengths_tbl AS D
INNER JOIN Org_Criteria_tbl AS O ON D.Ln Like "* " & O.Org_Criteria &
" *"

This successfully finds any Organization donors in the
Donor_Lengths_tbl
which are in the middle of the field Ln.

I'd like to also be able to also get the Organization donors in which
the
search term falls at the beginning or at the end of Ln field.

This didn't work for me:

SELECT *
FROM Donor_Lengths_tbl AS D
INNER JOIN Org_Criteria_tbl AS O ON D.Ln Like "* " & O.Org_Criteria &
" *"
OR INNER JOIN Org_Criteria_tbl AS O ON D.Ln Like O.Org_Critieria & " *"
OR INNER JOIN Org_Criteria_tbl AS O ON D.Ln Like "* " & O.Org_Criteria;

Other than running three separate queries using each of the above types
of
non-equi joins, is there another option?

Google searching for "multi-part non-equi join" and looking up
"non-equi
join" posts in this forum weren't fruitful so I thought I'd ask.

Thanks in advance,

Patrick Briggs,
Pasadena, CA
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

"non equi join query" 1
Use of Dsum in a non equi join query 6
using a form to search for records over odbc connection.. 1
Searching for records using a subform. 0
A Call for Reassessment 5

Top