?
Solved

MS Access Query Question

Posted on 2006-04-11
9
Medium Priority
?
258 Views
Last Modified: 2008-03-17
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.
0
Comment
Question by:mboulton
  • 4
  • 4
9 Comments
 
LVL 9

Expert Comment

by:raopsn
ID: 16429539
please post your query
0
 
LVL 1

Author Comment

by:mboulton
ID: 16429816
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
 
LVL 9

Expert Comment

by:raopsn
ID: 16430018
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 1

Author Comment

by:mboulton
ID: 16430312
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
 
LVL 25

Expert Comment

by:SStory
ID: 16430693
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
 
LVL 1

Author Comment

by:mboulton
ID: 16431269
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
 
LVL 9

Expert Comment

by:raopsn
ID: 16431625
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
 
LVL 1

Author Comment

by:mboulton
ID: 16431852
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
 
LVL 9

Accepted Solution

by:
raopsn earned 2000 total points
ID: 16435516
I am sorry, I thought of SQL Server...

Use INSTR instead of CHARINDEX in Access...
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question