Design view is ruining my SQL.

[kballing]

I have a big messy query, it looks like this: (about 1200 characters with 10 AND/OR statements)

[FONT=Times New Roman][FONT=Arial][FONT=Verdana]SELECT Patients.EMPI, [Patients].[LAST_NM] & ", " & [Patients].[FIRST_NM] & " " & [Patients].[MIDDLE_NM] AS PAT_NM, DateDiff('yyyy',[BIRTH_DT],Date()) AS AGE, Patients.SEX_CD, Providers.LAST_NM & ", " & Providers.FIRST_NM & " " & Providers.MIDDLE_NM AS PRVDR_NM, Patients.PCP_ID, Patients.SCP_ID[/FONT][/FONT][/FONT]
 
[FONT=Times New Roman][FONT=Times New Roman][FONT=Verdana]FROM Providers RIGHT JOIN (Patients INNER JOIN Screen ON Patients.EMPI = Screen.EMPI) ON Providers.PRVDR_ID = Patients.PCP_ID[/FONT]
 
[FONT=Verdana]WHERE (Patients.EMPI Like [Forms]![ScreenTool].[FILTEREMPI]& "*") AND [/FONT]
[FONT=Verdana](DateDiff('yyyy',[BIRTH_DT],Date()) Between [Forms]![ScreenTool].[FILTERAGEMIN] And [Forms]![ScreenTool].[FILTERAGEMAX]) AND [/FONT]
[FONT=Verdana](Patients.SEX_CD Like [Forms]![ScreenTool].[FILTERSEX] & "*") AND [/FONT]
[FONT=Verdana](Patients.FIRST_NM Like "*" & [Forms]![ScreenTool].[FILTERFIRST] & "*") AND [/FONT]
[FONT=Verdana](Patients.LAST_NM Like "*" & [Forms]![ScreenTool].[FILTERLAST] & "*") AND [/FONT]
[FONT=Verdana](Abs([Screen].[COMPLETED]+[Screen].[PASSED]+Not IsNull([Screen].[LETTER_DT])+Not IsNull([Screen].[REPLY_DT])+[Screen].[ENROLLED])<=[Forms]![ScreenTool].[FILTERSTATUS]) AND [/FONT]
[FONT=Verdana]( (Providers.REGION_NM Like [Forms]![ScreenTool].[FILTERREGION]) OR [/FONT]
[FONT=Verdana]([Forms]![ScreenTool].[FILTERREGION] is null) ) AND [/FONT]
[FONT=Verdana]( (Patients.PCP_ID Like [Forms]![ScreenTool].[FILTERPCP]) OR[/FONT]
[FONT=Verdana](Patients.SCP_ID Like [Forms]![ScreenTool].[FILTERPCP]) OR[/FONT]
[FONT=Verdana]([Forms]![ScreenTool].[FILTERPCP] is null));[/FONT]
[/FONT]
[/FONT]

After I have saved the query and opened it again in design view and saved it again, access turns it into a monster
It looks like this: (4700 characters 50+ AND/OR statements)

[FONT=Verdana]SELECT Patients.EMPI, [Patients].[LAST_NM] & ", " & [Patients].[FIRST_NM] & " " & [Patients].[MIDDLE_NM] AS PAT_NM, DateDiff('yyyy',[BIRTH_DT],Date()) AS AGE, Patients.SEX_CD, Providers.LAST_NM & ", " & Providers.FIRST_NM & " " & Providers.MIDDLE_NM AS PRVDR_NM, Patients.PCP_ID, Patients.SCP_ID[/FONT]
 
[FONT=Verdana]FROM Providers RIGHT JOIN (Patients INNER JOIN Screen ON Patients.EMPI = Screen.EMPI) ON Providers.PRVDR_ID = Patients.PCP_ID[/FONT]
 
[FONT=Verdana]WHERE (((Patients.EMPI) Like [Forms]![ScreenTool].[FILTEREMPI] & "*") AND ((DateDiff('yyyy',[BIRTH_DT],Date())) Between [Forms]![ScreenTool].[FILTERAGEMIN] And [Forms]![ScreenTool].[FILTERAGEMAX]) AND ((Patients.SEX_CD) Like [Forms]![ScreenTool].[FILTERSEX] & "*") AND ((Patients.PCP_ID) Like [Forms]![ScreenTool].[FILTERPCP]) AND ((Providers.REGION_NM) Like [Forms]![ScreenTool].[FILTERREGION]) AND ((Patients.FIRST_NM) Like "*" & [Forms]![ScreenTool].[FILTERFIRST] & "*") AND ((Patients.LAST_NM) Like "*" & [Forms]![ScreenTool].[FILTERLAST] & "*") AND ((Abs([Screen].[COMPLETED]+[Screen].[PASSED]+Not IsNull([Screen].[LETTER_DT])+Not IsNull([Screen].[REPLY_DT])+[Screen].[ENROLLED]))<=[Forms]![ScreenTool].[FILTERSTATUS])) OR (((Patients.EMPI) Like [Forms]![ScreenTool].[FILTEREMPI] & "*") AND ((DateDiff('yyyy',[BIRTH_DT],Date())) Between [Forms]![ScreenTool].[FILTERAGEMIN] And [Forms]![ScreenTool].[FILTERAGEMAX]) AND ((Patients.SEX_CD) Like [Forms]![ScreenTool].[FILTERSEX] & "*") AND ((Patients.SCP_ID) Like [Forms]![ScreenTool].[FILTERPCP]) AND ((Providers.REGION_NM) Like [Forms]![ScreenTool].[FILTERREGION]) AND ((Patients.FIRST_NM) Like "*" & [Forms]![ScreenTool].[FILTERFIRST] & "*") AND ((Patients.LAST_NM) Like "*" & [Forms]![ScreenTool].[FILTERLAST] & "*") AND ((Abs([Screen].[COMPLETED]+[Screen].[PASSED]+Not IsNull([Screen].[LETTER_DT])+Not IsNull([Screen].[REPLY_DT])+[Screen].[ENROLLED]))<=[Forms]![ScreenTool].[FILTERSTATUS])) OR (((Patients.EMPI) Like [Forms]![ScreenTool].[FILTEREMPI] & "*") AND ((DateDiff('yyyy',[BIRTH_DT],Date())) Between [Forms]![ScreenTool].[FILTERAGEMIN] And [Forms]![ScreenTool].[FILTERAGEMAX]) AND ((Patients.SEX_CD) Like [Forms]![ScreenTool].[FILTERSEX] & "*") AND ((Patients.PCP_ID) Like [Forms]![ScreenTool].[FILTERPCP]) AND ((Patients.FIRST_NM) Like "*" & [Forms]![ScreenTool].[FILTERFIRST] & "*") AND ((Patients.LAST_NM) Like "*" & [Forms]![ScreenTool].[FILTERLAST] & "*") AND ((Abs([Screen].[COMPLETED]+[Screen].[PASSED]+Not IsNull([Screen].[LETTER_DT])+Not IsNull([Screen].[REPLY_DT])+[Screen].[ENROLLED]))<=[Forms]![ScreenTool].[FILTERSTATUS]) AND (([Forms]![ScreenTool].[FILTERREGION]) Is Null)) OR (((Patients.EMPI) Like [Forms]![ScreenTool].[FILTEREMPI] & "*") AND ((DateDiff('yyyy',[BIRTH_DT],Date())) Between [Forms]![ScreenTool].[FILTERAGEMIN] And [Forms]![ScreenTool].[FILTERAGEMAX]) AND ((Patients.SEX_CD) Like [Forms]![ScreenTool].[FILTERSEX] & "*") AND ((Patients.SCP_ID) Like [Forms]![ScreenTool].[FILTERPCP]) AND ((Patients.FIRST_NM) Like "*" & [Forms]![ScreenTool].[FILTERFIRST] & "*") AND ((Patients.LAST_NM) Like "*" & [Forms]![ScreenTool].[FILTERLAST] & "*") AND ((Abs([Screen].[COMPLETED]+[Screen].[PASSED]+Not IsNull([Screen].[LETTER_DT])+Not IsNull([Screen].[REPLY_DT])+[Screen].[ENROLLED]))<=[Forms]![ScreenTool].[FILTERSTATUS]) AND (([Forms]![ScreenTool].[FILTERREGION]) Is Null)) OR (((Patients.EMPI) Like [Forms]![ScreenTool].[FILTEREMPI] & "*") AND ((DateDiff('yyyy',[BIRTH_DT],Date())) Between [Forms]![ScreenTool].[FILTERAGEMIN] And [Forms]![ScreenTool].[FILTERAGEMAX]) AND ((Patients.SEX_CD) Like [Forms]![ScreenTool].[FILTERSEX] & "*") AND ((Providers.REGION_NM) Like [Forms]![ScreenTool].[FILTERREGION]) AND ((Patients.FIRST_NM) Like "*" & [Forms]![ScreenTool].[FILTERFIRST] & "*") AND ((Patients.LAST_NM) Like "*" & [Forms]![ScreenTool].[FILTERLAST] & "*") AND ((Abs([Screen].[COMPLETED]+[Screen].[PASSED]+Not IsNull([Screen].[LETTER_DT])+Not IsNull([Screen].[REPLY_DT])+[Screen].[ENROLLED]))<=[Forms]![ScreenTool].[FILTERSTATUS]) AND (([Forms]![ScreenTool].[FILTERPCP]) Is Null)) OR (((Patients.EMPI) Like [Forms]![ScreenTool].[FILTEREMPI] & "*") AND ((DateDiff('yyyy',[BIRTH_DT],Date())) Between [Forms]![ScreenTool].[FILTERAGEMIN] And [Forms]![ScreenTool].[FILTERAGEMAX]) AND ((Patients.SEX_CD) Like [Forms]![ScreenTool].[FILTERSEX] & "*") AND ((Patients.FIRST_NM) Like "*" & [Forms]![ScreenTool].[FILTERFIRST] & "*") AND ((Patients.LAST_NM) Like "*" & [Forms]![ScreenTool].[FILTERLAST] & "*") AND ((Abs([Screen].[COMPLETED]+[Screen].[PASSED]+Not IsNull([Screen].[LETTER_DT])+Not IsNull([Screen].[REPLY_DT])+[Screen].[ENROLLED]))<=[Forms]![ScreenTool].[FILTERSTATUS]) AND (([Forms]![ScreenTool].[FILTERREGION]) Is Null) AND (([Forms]![ScreenTool].[FILTERPCP]) Is Null));[/FONT]

The query takes about 5 sec to run, but after the mutation it takes 7. The query is the rowsource of a listbox. I have VBA code that handles query sorting, but it breaks with all these criteria. How can I keep access from messing with my SQL?

 

[KenHigg]

Have you tried saving/building as a query?

 

[kballing]

Yes, I typed in the SQL originally, my concern is that end users will mess it up once my application is deployed. All it takes is opening and saving in design view.

Why can't access just do this?

Where condition1 AND condition 2 AND . . . AND (Condition A Or Condition B OR . . .)

instead of,

Where (condition1 AND condition2 . . . AND Condition A)
Or (Condition 1 AND condition2 . . AND Condition B) OR . . .

How do I prevent editing in design view or prevent changes to the query all together after deployment?

 

[KenHigg]

Several ways... One way is to just hide them. Another is to not allow users access to these objects. You're going to get a handfull of security suggestions here and none of them are 100% bullet proof.

 

[c_smithwick]

Why can't access just do this?

Where condition1 AND condition 2 AND . . . AND (Condition A Or Condition B OR . . .)

instead of,

Where (condition1 AND condition2 . . . AND Condition A)
Or (Condition 1 AND condition2 . . AND Condition B) OR . . .

Actually, the second statement, while longer, is the expected logical output for a computer program operating from a defined set of rules and algorithms. Access doesn't care about the length of your string statements when you ask it to build an SQL statement for you, just the logical equivalence of them. In reality consider the following:

Where (condition1 AND condition2 . . . AND Condition A) 
Or (Condition 1 AND condition2 . . AND Condition B) OR . . .
 
"condition1 AND condition2 . . . AND Condition A" can be more simply stated as condition X
 
and "Condition 1 AND condition2 . . AND Condition B" can be more simply stated as condition Y, so
 
Where (condition X) 
Or (Condition Y) ...
 
is actually the SIMPLEST way to represent your logic statement.  To your eyes it may look complex, 
ah but to the computer .... beauty after all is in the eye of the beholder.

The English language is a good analogy. Gramatically, with all of the exceptions and special cases for sentence structure it is a NIGHTMARE for someone to learn who's native language is much more gramatically "conservative", with few exceptions to the rule and special cases. Access is simply translating your (clear to you) English into language that represents the simplest logical equivalent - regardless of actually string component length - something which is highly visible to you, but very transparent to the computer.

 

[Simon_MT]

This would seem to be a catch all scenario, I may not have this correct but here are some pointers

For a start I would create a separate Query

1) AGE has already been declared

DateDiff('yyyy',[BIRTH_DT],Date()) AS AGE

So your Where clause can become

(AGE Between [Forms]![ScreenTool].[FILTERAGEMIN] And [Forms]![ScreenTool].[FILTERAGEMAX])

2) I'm at a loss on [Forms]![ScreenTool].[FILTERSTATUS]) criteria if you would be kind enough to explain.

3) Region

( (Providers.REGION_NM Like [Forms]![ScreenTool].[FILTERREGION]) OR 
[FONT=Verdana]([Forms]![ScreenTool].[FILTERREGION] is null) )[/FONT]

Can be replaced with a widcard as if Nothing is in the Region it should pick up all Regions:

(Providers.REGION_NM Like [Forms]![ScreenTool].[FILTERREGION] & "*"[FONT=Verdana])[/FONT]

4) PCP and SCP
The same probably applies to Patient_PCP and Patient_SCP

(Patients.PCP_ID Like [Forms]![ScreenTool].[FILTERPCP] & "*")
(Patients.SCP_ID Like [Forms]![ScreenTool].[FILTERPCP] & "*")

I notice the the filter is the same for PCP and SCP

Do your users want a combination of all these criteria or will they ask for information based on separate information:

The Patient
The FilterStatus
The Region
The PCP or SCP

Simon

 

[kballing]

Thanks for the suggestions. I'll do what I can to revise things.

Some more details:
Can't use a wildcard in PCP_ID or SCP_ID, because some ID numbers are included in others. e.g. *123* also gives 123XX, XX123. PCP (primary care provider), SCP (secondary care provider). The user selects a doctor to list their patients. They want to see all the doctors patients regardless of if they are primary or secondary.


Region* wouldn't work, but *Region would. Some values are 'rural', 'rural north', 'south', 'southwest', etc.

As for the FILTERSTATUS stuff. In the screen table there are 5 fields that determine the status of a patient, but I will definitely have to change the design of my table because this is too time consuming.

All these criteria are critical.

 

[kballing]

Here's what my final query looks like.

SELECT Patients.EMPI, Patients.LAST_NM & ", " & Patients.FIRST_NM & " " & Patients.MIDDLE_NM AS PAT_NM, DateDiff("yyyy",BIRTH_DT,Date()) AS AGE, Patients.SEX_CD, Providers.LAST_NM & ", " & Providers.FIRST_NM & " " & Providers.MIDDLE_NM AS PRVDR_NM
FROM Providers RIGHT JOIN (Patients INNER JOIN Screen ON Patients.EMPI = Screen.EMPI) ON Providers.PRVDR_ID = Patients.PCP_ID
WHERE (((Patients.EMPI) Like [Forms]![ScreenTool].[FILTEREMPI] & "*") AND ((Patients.SEX_CD) Like [Forms]![ScreenTool].[FILTERSEX] & "*") AND ((Patients.FIRST_NM) Like "*" & [Forms]![ScreenTool].[FILTERFIRST] & "*") AND ((Patients.LAST_NM) Like "*" & [Forms]![ScreenTool].[FILTERLAST] & "*") AND ((DateDiff("yyyy",[BIRTH_DT],Date())) Between [Forms]![ScreenTool].[FILTERAGEMIN] And [Forms]![ScreenTool].[FILTERAGEMAX]) AND ((Screen.STATUS)=[Forms]![ScreenTool].[FILTERSTATUS]) AND ((Providers.REGION_NM) Like "*" & [Forms]![ScreenTool].[FILTERREGION]) AND ((Patients.PCP_ID) Like [Forms]![ScreenTool].[FILTERPCP])) OR (((Patients.EMPI) Like [Forms]![ScreenTool].[FILTEREMPI] & "*") AND ((Patients.SEX_CD) Like [Forms]![ScreenTool].[FILTERSEX] & "*") AND ((Patients.FIRST_NM) Like "*" & [Forms]![ScreenTool].[FILTERFIRST] & "*") AND ((Patients.LAST_NM) Like "*" & [Forms]![ScreenTool].[FILTERLAST] & "*") AND ((DateDiff("yyyy",[BIRTH_DT],Date())) Between [Forms]![ScreenTool].[FILTERAGEMIN] And [Forms]![ScreenTool].[FILTERAGEMAX]) AND ((Screen.STATUS)=[Forms]![ScreenTool].[FILTERSTATUS]) AND ((Providers.REGION_NM) Like "*" & [Forms]![ScreenTool].[FILTERREGION]) AND ((Patients.SCP_ID) Like [Forms]![ScreenTool].[FILTERPCP])) OR (((Patients.EMPI) Like [Forms]![ScreenTool].[FILTEREMPI] & "*") AND ((Patients.SEX_CD) Like [Forms]![ScreenTool].[FILTERSEX] & "*") AND ((Patients.FIRST_NM) Like "*" & [Forms]![ScreenTool].[FILTERFIRST] & "*") AND ((Patients.LAST_NM) Like "*" & [Forms]![ScreenTool].[FILTERLAST] & "*") AND ((DateDiff("yyyy",[BIRTH_DT],Date())) Between [Forms]![ScreenTool].[FILTERAGEMIN] And [Forms]![ScreenTool].[FILTERAGEMAX]) AND ((Screen.STATUS)=[Forms]![ScreenTool].[FILTERSTATUS]) AND ((Providers.REGION_NM) Like "*" & [Forms]![ScreenTool].[FILTERREGION]) AND (([Forms]![ScreenTool].[FILTERPCP]) Is Null Or ([Forms]![ScreenTool].[FILTERPCP])=""));

This is after the Design View mutation and it is about half the length. I got rid of the absolute value statement and had that calculation done when the record is updated and stored to a new STATUS field in my Screen table. It still runs pretty slow, but a lot better than before. It only takes a second or two to requery. And I found the reason for the problems in my vba. I had a typo in one of my column names.