Table as Query Criteria w/ Like?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TooFolkGR - 11 Nov 2005 16:45 GMT

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

--
TooFolkG

Reply to this Message

John Spencer - 11 Nov 2005 19:59 GMT

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.

> Currently I have this query:
>
[quoted text clipped - 14 lines]
> an exact match. I figured this would involve putting a LIKE
> somewhere... that's about as far as I got. Any suggestions?

Reply to this Message

TooFolkGR - 11 Nov 2005 21:00 GMT

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

--
TooFolkG

Reply to this Message

Tom Ellison - 11 Nov 2005 21:04 GMT

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

> John,
>
[quoted text clipped - 14 lines]
>
> -Adam

Reply to this Message