Like Join Query

Q

Query Curious

I know this has been covered before, can't seem to make it work. What's the
SQL?

I've got 2 tables:
Table Criteria
Criteria.PartStub
AA
BB

Table MainData
MainData.PartNumber
AADog
AACat
AAFrog
BBDog
BBCat
BBFrog

I want to query the "MainData" table, but do it based
on Criteria.PartStub & " * "

(..I'm not looking to manually enter the Criteria stubs into the GUI designer
as I've got hundreds of part stubs..)

Any help would be appreciated!
 
R

Roger Carlson

SELECT PartNumber.PartNumber
FROM PartNumber INNER JOIN Criteria ON PartNumber.PartNumber like
Criteria.PartStub & "*";

or

SELECT PartNumber.PartNumber
FROM PartNumber, Criteria
WHERE PartNumber.PartNumber Like PartStub & "*";

The first one cannot be shown in the Design View, only in SQL View. The
second one can be see in both Views.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Q

Query Curious

Ooh man...THANK YOU ROGER!

This thing has been bugging the heck out of me..it
was driving me nuts! I got my example to run, as
well as the main/real problem (wow, did it take a
while to run!..lots of items to match w/ the wild car
criteria table..)

You the man, have a great labor day weekend!
 
M

Michel Walsh

Hi,


The indexes CAN be used if the wildcard comes AFTER.

Since we have

LIKE fieldName & "*"

the index can be used. It could NOT, as counter example, with:

LIKE "*" & fieldName

It is like looking, in the telephone book, for someone with a family name
starting with "P", by opposition to search for someone with the family name
having a "P" in second position of its name. The first case is faster than
the second, where you basically have to scan the whole telephone book.


Vanderghast, Access MVP
 
Top