Table as Query Criteria w/ Like?

T

TooFolkGR

Currently I have this query:

SELECT tblSiteSetups.CombinedStorefront, *
FROM tblSiteSetups
WHERE (((tblSiteSetups.CombinedStorefront)=Yes) AN
((tblSiteSetups.siteCustomerNumbers) In (SELECT CustomerNumbers FRO
tblWork)));

It -works- in that it returns all records in tblSiteSetups that matc
the exact customer numbers in tblWork, but I want it to return al
records in tblSiteSetups that even -contain- customer numbers i
tblWork.

For example, if tblWork has customer number "55" in it, and a record i
tblSiteSetups has Customer Numbers "45, 55, 67, 99" I want it to retur
that record. Currently it's -only - returning records where there i
an exact match. I figured this would involve putting a LIK
somewhere... that's about as far as I got. Any suggestions
 
J

John Spencer

The best piece of advice is to fix your data so you aren't storing multiple
pieces of data in the same field.

If you can't then you can probably use a non-equi join

SELECT S.CombinedStorefront, *
FROM tblSiteSetups as S INNER JOIN tblWork as W
ON S.SiteCustomerNumber LIKE "*" & W.CustomerNumbers & "*"
WHERE S.CombinedStorefront=Yes

You can make it a little better by using.
That is still like to get mismatches if you can have numbers like 47 and 477
or 7 and 77.
 
T

TooFolkGR

John,

Thanks so much for that, it works perfectly.

I agree 100% about not storing multiple pieces of data in the sam
field. The problem is that the database is populated by exce
spreadsheets that are filled out by salespeople. It is impossible t
enforce any kind of consistency as far as the way the customer number
are entered.

As for it returning inaccurate records, that should be a low risk. Th
customer numbers I'm actually dealing with are all nine digits long, an
unique. It's just that sometimes there are multiple Customer Number
strung together.

Thanks again for all your help!

-Ada
 
T

Tom Ellison

Dear Adam:

The key here would be to parse the data into proper structure at the time it
is imported, rather than having to parse it every time you query the
database.

Tom Ellison
 
Top