[AccessD] Query question

Heenan, Lambert Lambert.Heenan at chartisinsurance.com
Thu Jun 3 08:31:32 CDT 2010


"-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee
Sent: Wednesday, June 02, 2010 5:08 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Query question

That's so weird. It's basically saying WHERE True = -1   or WHERE False =-1"
----------------------------

That is exactly the point. The query has a where clause. 
Lets break it down...


WHERE ((([Status]='WAGC' And [Design_Fluid_Status_Id]='WI') Or 
 ([Status]='WAGW' And [Design_Fluid_Status_Id]='CI') Or 
 ([Status]='CI' And [Design_Fluid_Status_Id]='WI') Or ([Status]='WI' 
 And [Design_Fluid_Status_Id]='CI'))=-1)


Is logically equivalent to

Where ((Condition) or (Condition) Or (Condition) or (Condition) = -1)

Which in turn is the same as

Where ((Condition) or (Condition) Or (Condition) or (Condition) = True)

Note that all the conditions are logically combined with the OR operation. So, if *any one* of those four conditions evaluate to true, the entire where clause is true, therefore *all* of the records will be returned.

Changing the where clause to

WHERE (([Status]='WAGC' And [Design_Fluid_Status_Id]='WI') Or 
 ([Status]='WAGW' And [Design_Fluid_Status_Id]='CI') Or 
 ([Status]='CI' And [Design_Fluid_Status_Id]='WI') Or ([Status]='WI' 
 And [Design_Fluid_Status_Id]='CI'))

Which is logically Where ((Condition) or (Condition) Or (Condition) or (Condition))

Or the more succinct version 

WHERE ([Status]='WAGC' And ([Design_Fluid_Status_Id]='WI') Or 
 [Design_Fluid_Status_Id]='CI') Or 
 ([Status]='CI' And ([Design_Fluid_Status_Id]='WI' or [Design_Fluid_Status_Id]='CI'))


Will select only the records that actually meet the conditions (apparently 4 of them fit the mold).

Hope this clarifies things a bit.

Lambert





More information about the AccessD mailing list