[AccessD] What is the cost

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