Expression too complex

[seanybravo]

I have a website that uses a pick list to select what categories you wish to include a database. The problem that I have come accross is that if the user selects too many categories the SQL query becomes too complex. I know that I will have to rethink my query but have no idea on how I can simplify things. Any suggestions would be great.

This is the example of my SQL statement with only two selected categories. As you can imagine the WHERE statement grows exponentially when more categories are selected.

SELECT tblData.*, tblTitle.*, tblCustomCategory.* FROM ((((( tblData LEFT JOIN (SELECT * FROM tblTitle IN 'E:\Websites\ebusinessdirectories\database\admin.mdb') AS tblTitle ON tblData.fldContactTitle = tblTitle.fldTitleID) LEFT JOIN (SELECT * FROM tblCategoryList IN 'E:\Websites\ebusinessdirectories\database\admin.mdb') AS tblCustomCategory ON tblData.fldCategory1 = tblCustomCategory.fldID) LEFT JOIN (SELECT * FROM tblCategoryList IN 'E:\Websites\ebusinessdirectories\database\admin.mdb') AS tblCustomCategory1 ON tblData.fldCategory2 = tblCustomCategory1.fldID) LEFT JOIN (SELECT * FROM tblCategoryList IN 'E:\Websites\ebusinessdirectories\database\admin.mdb') AS tblCustomCategory2 ON tblData.fldCategory3 = tblCustomCategory2.fldID) LEFT JOIN (SELECT * FROM tblCategoryList IN 'E:\Websites\ebusinessdirectories\database\admin.mdb') AS tblCustomCategory3 ON tblData.fldCategory4 = tblCustomCategory3.fldID) WHERE (tblCustomCategory.fldDefinition LIKE '%Abnormal load transport specialist%' OR tblCustomCategory1.fldDefinition LIKE '%Abnormal load transport specialist%' OR tblCustomCategory2.fldDefinition LIKE '%Abnormal load transport specialist%' OR tblCustomCategory3.fldDefinition LIKE '%Abnormal load transport specialist%' OR tblCustomCategory.fldDefinition LIKE '%Access equipment%' OR tblCustomCategory1.fldDefinition LIKE '%Access equipment%' OR tblCustomCategory2.fldDefinition LIKE '%Access equipment%' OR tblCustomCategory3.fldDefinition LIKE '%Access equipment%')

 

[PHV]

Why use tblCategoryList so many times ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dhookom]

This looks like a normalization issue since there are fldCategory 1-4. Can you explain your table structure?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]