MS Access Query Question

I'm using MSAccess vie OLEDB from a web page so the query comes from an ASP page, the way i'm doing it is trying every possibility in an OR statement, but i get an error saying Query too Complex from OLE DB.

I have 3 tables with 5 fields (see below)
I want to be able to create a search for any combination of 3 of the 5 fields in any order, meaning
If i have searchstring1, searchstring2 & searchstring3, i want to be able to find them in any order in the database. They could be in any of the fields. For instance searchstring1 could be in Table2 Field3 while searchstring2 could be in Table1 Field2.


Table1

Field1
Field2

Table2

Field3
Field4

Table3

Field5

I hope this makes sense.
Let me know.
LVL 1
mboultonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

raopsnCommented:
please post your query
0
mboultonAuthor Commented:
My query is way too long,
it's basically
Select Field1, Field2, Field3, Field4, Field5 from AllTables where
Field1 like 'searchstring1' and field2 like 'searchsting2' or
Field1 like 'searchstring2' and field2 like 'searchsting1' or
Field1 like 'searchstring1' and field2 like 'searchsting3

etc.. but it's ridiculously long because of all of the possibilities, and i guess that's why it failed in the first place, it was too long.
0
raopsnCommented:
try to enclose the combinations in brackets like this:

Select Field1, Field2, Field3, Field4, Field5 from AllTables where
(Field1 like 'searchstring1' and field2 like 'searchsting2')  OR
(Field1 like 'searchstring2' and field2 like 'searchsting1')  OR
(Field1 like 'searchstring1' and field2 like 'searchsting3)
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

mboultonAuthor Commented:
it's not actually the select statement that's wrong, it's that with 3 search strings in five fields there are about 60 different combinations possible, and i want to figure a way to do it without writing out each OR string.
0
SStoryCommented:
Is it possible that you have a design flaw in the tables?  Not knowing what they represent makes it harder for us to really give a good answer.  Perhaps there is a better way of design that would allow easier search queries.

Just a thought....
0
mboultonAuthor Commented:
True, i may have, but since they're already populated with about 8000 records and there are pages tied to them, i'd rather not change them if possible.
0
raopsnCommented:
You can try something like this:

Select Field1, Field2, Field3, Field4, Field5
from AllTables
Where  CHARINDEX('searchstring1', Field1 + Field2 + Field3 + Field4 + Field5) > 0
OR CHARINDEX('searchstring2', Field1 + Field2 + Field3 + Field4 + Field5) > 0
OR CHARINDEX('searchstring3', Field1 + Field2 + Field3 + Field4 + Field5) > 0
0
mboultonAuthor Commented:
I like this idea, will this work in MS Access though? I know it works on MS Sql, but i didn't think it would work for Access
0
raopsnCommented:
I am sorry, I thought of SQL Server...

Use INSTR instead of CHARINDEX in Access...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.