[AccessD] What is the cost

Thanks AD.

I have been doing the query on query for a long time and just decided to see if there was an 
alternative.

John W. Colby
www.ColbyConsulting.com


A.D.Tejpal wrote:
> John,
> 
>     In tests conducted at my end (Access 2003 desktop on Win XP),  a single query having third calculated field F_C, referring to other two calculated fields (F_A and F_B) is found to work smoothly. However, even if no error is encountered, such a style is considered risky and a strict No-No, fraught with the potential for inconsistent results under certain combination of circumstances. Sample query named Q_JC_SingleQuery_RiskyStyle as given below, demonstrates this style.
> 
>     As an academic exercise, if one were to implement the goal within a single query in a safe manner, it becomes necessary to refer to the expressions of calculated fields F_A and F_B (not their names) in the expression for third calculated field F_C. This approach is demonstrated in sample query named Q_JC_SingleQuery_PreferredStyle as given below.
> 
>     However, a single query devised as per previous para tends to become messy. A much neater approach lies in adoption of query upon query, as you have rightly been doing so far. Sample query Q_JC_1 given below represents the first stage, with no cross reference amongst any calculated fields. For its calculated field F_C, final query Q_JC_2 is in a position to safely refer to calculated fields F_A and F_B in the source query Q_JC_1.
> 
>     Extra overhead involved in using query on query instead of a single query is not likely to be of any material significance as in any case, the underlying calculations get performed afresh even when a calculated field is referred by name, whether in query upon query or within a single query.
> 
> Best wishes,
> A.D. Tejpal
> ------------
> 
> Q_JC_SingleQuery_RiskyStyle
> Calculated field F_C refers directly to
> calculated fields F_A and F_B
> ==============================
> SELECT MyTable.*, IIf([F1] Mod 2=0,True,False) AS F_A, IIf([F2] Mod 4=0,True,False) AS F_B, Not [F_A] And Not [F_B] AS F_C  
> FROM MyTable;
> ==============================
> 
> Q_JC_SingleQuery_PreferredStyle
> Calculated field F_C refers to expressions of
> calculated fields F_A and F_B
> ==============================
> SELECT MyTable.*, IIf([F1] Mod 2=0,True,False) AS F_A, IIf([F2] Mod 4=0,True,False) AS F_B, IIf(IIf([F1] Mod 2=0,True,False)=False And IIf([F2] Mod 4=0,True,False)=False,True,False) AS F_C  
> FROM MyTable;
> ==============================
> 
> Q_JC_1 (First stage query)
> No cross-reference between calculated fields
> ==============================
> SELECT MyTable.*, IIf([F1] Mod 2=0,True,False) AS F_A, IIf([F2] Mod 4=0,True,False) AS F_B  
> FROM MyTable;
> ==============================
> 
> Q_JC_2 (2nd stage - Query upon query)
> Calculated fields (F_A and F_B) of source query 
> Q_JC_1 can be referred safely.
> ==============================
> SELECT Q_JC_1.*, Not [F_A] And Not [F_B] AS F_C  
> FROM Q_JC_1;
> ==============================
> 
>   ----- Original Message ----- 
>   From: jwcolby 
>   To: Access Developers discussion and problem solving 
>   Sent: Friday, August 21, 2009 21:08
>   Subject: [AccessD] What is the cost
> 
> 
>   Does anyone know the cost of building a base query, then a query on top of that query with a where clause in the top query.
> 
>   And is there any other way to do this...
> 
>   I need to build a boolean true / false in two fields FieldA and FieldB, then a third field - Where not FieldA and Not FieldB.
> 
>   Trying to do this directly in one query gives an error message "does not recognize FieldA".  In the past I always just built a "base query" where I build up the boolean fields FieldA and FieldB, then create a query on top of that which does the final where not fielda and not fieldb.
> 
>   this does work because FieldA and FieldB now exist to the top level query, but of course it causes me to have to create two queries etc.
> 
>   Can this be done in a single level?  In theory you could put whatever you are doing to create the boolean values into a single field, but it is not "visible", i.e. you cannot look down through the data and SEE whether the records create boolean trues and falses.
> 
>   So I want to create the two boolean fields, and then use those fields in a third field.  ATM Jet is objecting.
>   -- 
>   John W. Colby
>   www.ColbyConsulting.com