SQL masters Simplify SQL code in Query

[ram_rtg]

Hi all,

I have a "search as you type query" that I have created based on the sample database here:
http://www.access-programmers.co.uk/forums/showthread.php?t=188663

In addition to the search code I have included filters on the form.

When viewing the query it goes down 20 rows and I've been told this is poor programing therefore I would like to see if anyone can identify some repetition in the code that is un-necessary.

the code below:

SELECT tblData.ID, tblData.[Release date], tblData.Title, tblData.Status, tblData.[Staff Name], tblData.[Team notes], tblData.[Progress notes], tblData.[Costing notes], tblData.[Costing contact], tblData.[Transfer details], tblData.[Costing Request Number], tblData.[Contact name], tblData.[Reference Number], tblData.Group
FROM tblData
WHERE (((tblData.ID) ALike "%" & [forms]![frmSearch]![SrchText] & "%") AND ((tblData.[Release date]) Between [forms]![frmSearch]![txtdatefrom] And [forms]![frmSearch]![txtdateto]) AND ((tblData.Status) ALike "%" & [Forms]![frmSearch]![StatusFilter] & "%") AND ((tblData.[Staff Name]) ALike "%" & [Forms]![frmSearch]![AuthorFilter] & "%") AND ((tblData.Group) ALike "%" & [Forms]![frmSearch]![ThemeFilter] & "%")) OR (((tblData.ID) ALike "%" & [forms]![frmSearch]![SrchText] & "%") AND ((tblData.[Release date]) ALike "%") AND ((tblData.Status) ALike "%" & [Forms]![frmSearch]![StatusFilter] & "%") AND ((tblData.[Staff Name]) ALike "%" & [Forms]![frmSearch]![AuthorFilter] & "%") AND ((tblData.Group) ALike "%" & [Forms]![frmSearch]![ThemeFilter] & "%") AND (([forms]![frmSearch]![txtdatefrom]) Is Null) AND (([forms]![frmSearch]![txtdateto]) Is Null)) OR (((tblData.[Release date]) Between [forms]![frmSearch]![txtdatefrom] And [forms]![frmSearch]![txtdateto]) AND ((tblData.Title) ALike "%" & [forms]![frmSearch]![SrchText] & "%") AND ((tblData.Status) ALike "%" & [Forms]![frmSearch]![StatusFilter] & "%") AND ((tblData.[Staff Name]) ALike "%" & [Forms]![frmSearch]![AuthorFilter] & "%") AND ((tblData.Group) ALike "%" & [Forms]![frmSearch]![ThemeFilter] & "%")) OR (((tblData.[Release date]) ALike "%") AND ((tblData.Title) ALike "%" & [forms]![frmSearch]![SrchText] & "%") AND ((tblData.Status) ALike "%" & [Forms]![frmSearch]![StatusFilter] & "%") AND ((tblData.[Staff Name]) ALike "%" & [Forms]![frmSearch]![AuthorFilter] & "%") AND ((tblData.Group) ALike "%" & [Forms]![frmSearch]![ThemeFilter] & "%") AND (([forms]![frmSearch]![txtdatefrom]) Is Null) AND (([forms]![frmSearch]![txtdateto]) Is Null)) OR (((tblData.[Release date]) Between [forms]![frmSearch]![txtdatefrom] And [forms]![frmSearch]![txtdateto]) AND ((tblData.Status) ALike "%" & [Forms]![frmSearch]![StatusFilter] & "%") AND ((tblData.[Staff Name]) ALike "%" & [Forms]![frmSearch]![AuthorFilter] & "%") AND ((tblData.[Team notes]) ALike "%" & [forms]![frmSearch]![SrchText] & "%") AND ((tblData.Group) ALike "%" & [Forms]![frmSearch]![ThemeFilter] & "%")) OR (((tblData.[Release date]) ALike "%") AND ((tblData.Status) ALike "%" & [Forms]![frmSearch]![StatusFilter] & "%") AND ((tblData.[Staff Name]) ALike "%" & [Forms]![frmSearch]![AuthorFilter] & "%") AND ((tblData.[Team notes]) ALike "%" & [forms]![frmSearch]![SrchText] & "%") AND ((tblData.Group) ALike "%" & [Forms]![frmSearch]![ThemeFilter] & "%") AND (([forms]![frmSearch]![txtdatefrom]) Is Null) AND (([forms]![frmSearch]![txtdateto]) Is Null)) OR (((tblData.[Release date]) Between [forms]![frmSearch]![txtdatefrom] And [forms]![frmSearch]![txtdateto]) AND ((tblData.Status) ALike "%" & [Forms]![frmSearch]![StatusFilter] & "%") AND ((tblData.[Staff Name]) ALike "%" & [Forms]![frmSearch]![AuthorFilter] & "%") AND ((tblData.[Progress notes]) ALike "%" & [forms]![frmSearch]![SrchText] & "%") AND ((tblData.Group) ALike "%" & [Forms]![frmSearch]![ThemeFilter] & "%")) OR (((tblData.[Release date]) ALike "%") AND ((tblData.Status) ALike "%" & [Forms]![frmSearch]![StatusFilter] & "%") AND ((tblData.[Staff Name]) ALike "%" & [Forms]![frmSearch]![AuthorFilter] & "%") AND ((tblData.[Progress notes]) ALike "%" & [forms]![frmSearch]![SrchText] & "%") AND ((tblData.Group) ALike "%" & [Forms]![frmSearch]![ThemeFilter] & "%") AND (([forms]![frmSearch]![txtdatefrom]) Is Null) AND (([forms]![frmSearch]![txtdateto]) Is Null)) OR (((tblData.[Release date]) Between [forms]![frmSearch]![txtdatefrom] And [forms]![frmSearch]![txtdateto]) AND ((tblData.Status) ALike "%" & [Forms]![frmSearch]![StatusFilter] & "%") AND ((tblData.[Staff Name]) ALike "%" & [Forms]![frmSearch]![AuthorFilter] & "%") AND ((tblData.[Costing notes]) ALike "%" & [forms]![frmSearch]![SrchText] & "%") AND ((tblData.Group) ALike "%" & [Forms]![frmSearch]![ThemeFilter] & "%")) OR (((tblData.[Release date]) ALike "%") AND ((tblData.Status) ALike "%" & [Forms]![frmSearch]![StatusFilter] & "%") AND ((tblData.[Staff Name]) ALike "%" & [Forms]![frmSearch]![AuthorFilter] & "%") AND ((tblData.[Costing notes]) ALike "%" & [forms]![frmSearch]![SrchText] & "%") AND ((tblData.Group) ALike "%" & [Forms]![frmSearch]![ThemeFilter] & "%") AND (([forms]![frmSearch]![txtdatefrom]) Is Null) AND (([forms]![frmSearch]![txtdateto]) Is Null)) OR (((tblData.[Release date]) Between [forms]![frmSearch]![txtdatefrom] And [forms]![frmSearch]![txtdateto]) AND ((tblData.Status) ALike "%" & [Forms]![frmSearch]![StatusFilter] & "%") AND ((tblData.[Staff Name]) ALike "%" & [Forms]![frmSearch]![AuthorFilter] & "%") AND ((tblData.[Costing contact]) ALike "%" & [forms]![frmSearch]![SrchText] & "%") AND ((tblData.Group) ALike "%" & [Forms]![frmSearch]![ThemeFilter] & "%")) OR (((tblData.[Release date]) ALike "%") AND ((tblData.Status) ALike "%" & [Forms]![frmSearch]![StatusFilter] & "%") AND ((tblData.[Staff Name]) ALike "%" & [Forms]![frmSearch]![AuthorFilter] & "%") AND ((tblData.[Costing contact]) ALike "%" & [forms]![frmSearch]![SrchText] & "%") AND ((tblData.Group) ALike "%" & [Forms]![frmSearch]![ThemeFilter] & "%") AND (([forms]![frmSearch]![txtdatefrom]) Is Null) AND (([forms]![frmSearch]![txtdateto]) Is Null)) OR (((tblData.[Release date]) Between [forms]![frmSearch]![txtdatefrom] And [forms]![frmSearch]![txtdateto]) AND ((tblData.Status) ALike "%" & [Forms]![frmSearch]![StatusFilter] & "%") AND ((tblData.[Staff Name]) ALike "%" & [Forms]![frmSearch]![AuthorFilter] & "%") AND ((tblData.[Transfer details]) ALike "%" & [forms]![frmSearch]![SrchText] & "%") AND ((tblData.Group) ALike "%" & [Forms]![frmSearch]![ThemeFilter] & "%")) OR (((tblData.[Release date]) ALike "%") AND ((tblData.Status) ALike "%" & [Forms]![frmSearch]![StatusFilter] & "%") AND ((tblData.[Staff Name]) ALike "%" & [Forms]![frmSearch]![AuthorFilter] & "%") AND ((tblData.[Transfer details]) ALike "%" & [forms]![frmSearch]![SrchText] & "%") AND ((tblData.Group) ALike "%" & [Forms]![frmSearch]![ThemeFilter] & "%") AND (([forms]![frmSearch]![txtdatefrom]) Is Null) AND (([forms]![frmSearch]![txtdateto]) Is Null)) OR (((tblData.[Release date]) Between [forms]![frmSearch]![txtdatefrom] And [forms]![frmSearch]![txtdateto]) AND ((tblData.Status) ALike "%" & [Forms]![frmSearch]![StatusFilter] & "%") AND ((tblData.[Staff Name]) ALike "%" & [Forms]![frmSearch]![AuthorFilter] & "%") AND ((tblData.[Costing Request Number]) ALike "%" & [forms]![frmSearch]![SrchText] & "%") AND ((tblData.Group) ALike "%" & [Forms]![frmSearch]![ThemeFilter] & "%")) OR (((tblData.[Release date]) ALike "%") AND ((tblData.Status) ALike "%" & [Forms]![frmSearch]![StatusFilter] & "%") AND ((tblData.[Staff Name]) ALike "%" & [Forms]![frmSearch]![AuthorFilter] & "%") AND ((tblData.[Costing Request Number]) ALike "%" & [forms]![frmSearch]![SrchText] & "%") AND ((tblData.Group) ALike "%" & [Forms]![frmSearch]![ThemeFilter] & "%") AND (([forms]![frmSearch]![txtdatefrom]) Is Null) AND (([forms]![frmSearch]![txtdateto]) Is Null)) OR (((tblData.[Release date]) Between [forms]![frmSearch]![txtdatefrom] And [forms]![frmSearch]![txtdateto]) AND ((tblData.Status) ALike "%" & [Forms]![frmSearch]![StatusFilter] & "%") AND ((tblData.[Staff Name]) ALike "%" & [Forms]![frmSearch]![AuthorFilter] & "%") AND ((tblData.[Contact name]) ALike "%" & [forms]![frmSearch]![SrchText] & "%") AND ((tblData.Group) ALike "%" & [Forms]![frmSearch]![ThemeFilter] & "%")) OR (((tblData.[Release date]) ALike "%") AND ((tblData.Status) ALike "%" & [Forms]![frmSearch]![StatusFilter] & "%") AND ((tblData.[Staff Name]) ALike "%" & [Forms]![frmSearch]![AuthorFilter] & "%") AND ((tblData.[Contact name]) ALike "%" & [forms]![frmSearch]![SrchText] & "%") AND ((tblData.Group) ALike "%" & [Forms]![frmSearch]![ThemeFilter] & "%") AND (([forms]![frmSearch]![txtdatefrom]) Is Null) AND (([forms]![frmSearch]![txtdateto]) Is Null)) OR (((tblData.[Release date]) Between [forms]![frmSearch]![txtdatefrom] And [forms]![frmSearch]![txtdateto]) AND ((tblData.Status) ALike "%" & [Forms]![frmSearch]![StatusFilter] & "%") AND ((tblData.[Staff Name]) ALike "%" & [Forms]![frmSearch]![AuthorFilter] & "%") AND ((tblData.[Reference Number]) ALike "%" & [forms]![frmSearch]![SrchText] & "%") AND ((tblData.Group) ALike "%" & [Forms]![frmSearch]![ThemeFilter] & "%")) OR (((tblData.[Release date]) ALike "%") AND ((tblData.Status) ALike "%" & [Forms]![frmSearch]![StatusFilter] & "%") AND ((tblData.[Staff Name]) ALike "%" & [Forms]![frmSearch]![AuthorFilter] & "%") AND ((tblData.[Reference Number]) ALike "%" & [forms]![frmSearch]![SrchText] & "%") AND ((tblData.Group) ALike "%" & [Forms]![frmSearch]![ThemeFilter] & "%") AND (([forms]![frmSearch]![txtdatefrom]) Is Null) AND (([forms]![frmSearch]![txtdateto]) Is Null));

I look forward to your recommendations.

 

[plog]

I have no specific advice (would envy-pity anyone who actually does take the time to weed through all that to give you any), but I do have some general advice: simplify your expression like it was a mathematical expression.

Treat that horrendous WHERE clause like a long algebraic statement you want to reduce and simplify. Suppose you had this:


(8a + 16b) + (4a + 8b) + (7a*3b)

You would find like terms, move them together and reduce the equation:

2(4a + 8b) + (4a + 8b) + 7a*3b

3(4a + 8b) + 3*7ab

3(4a + 8b + 7ab)

If you have an term that is repeated in the WHERE clause, simplify it by moving it further outside the equation.

 

[jdraw]

Just to say I agree with plog --- simplify. Get a clear statement of what you're trying to do, simplify - then write the code.

Also, start with a simple WHERE clause - 1 or 2 terms - and ensure it works. Then gradually add complexity, and ensure it works at every stage.

Perhaps you should also review this Allen Browne search

 

[ram_rtg]

I guess because there are 16 fields to search through the code will always be large.
Thank you all very much for taking the time to post ideas here.
I will take your suggestions into consideration.

 

[jdraw]

Not necessarily see this example

The secret is to clearly identify what you are trying to do. Work with pencil and paper and test out some ideas. Don't jump into Access until you have some concept that you have tested. Too many new to database and Access think Access is going to solve something for them. In reality, Access does what you tell it.