[AccessD] Cnts in sets

    It is to be kept in view that final query Q_3 involves Cartesian join between totals query Q_2 and table T_A. For large data sets, such a query can become very slow. 

    In such a situation, you can get dramatic improvement in speed by storing the output of query  Q_2  into an interim table (say T_2) and then use T_2 (in lieu of Q_2) in the final query (Q_3).

A.D.Tejpal
------------

  ----- Original Message ----- 
  From: jwcolby 
  To: 'Access Developers discussion and problem solving' 
  Sent: Saturday, March 01, 2008 22:37
  Subject: Re: [AccessD] Cnts in sets


  Thanks AD, I will give it a try. 

  John W. Colby
  Colby Consulting
  www.ColbyConsulting.com 
  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
  [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.Tejpal
  Sent: Saturday, March 01, 2008 11:35 AM
  To: Access Developers discussion and problem solving
  Subject: Re: [AccessD] Cnts in sets

      Apparently, you wish to display the missing PkID's where the hole size
  (consecutive missing values) is large, say >= 1000.

      Let table T_B be the table having missing PkID's. Set up table T_A with
  single field PkID and populate it with sequential numbers covering the whole
  range, without any holes.

      Sample query Q_3, as given below, should get you the desired results. It
  uses preliminary queries Q_1 & Q_2, along with function Fn_Mark() given
  below.

      Note - This solution is expected to be much faster than those using
  subqueries or recordsets. As stated earlier, this is based upon Access 2003
  desktop.

  A.D.Tejpal
  ------------

  Q_3  (Final Query)
  Displays missing PkID's
  (Where hole size >= 1000 consecutive missing values)
  ===================================
  SELECT Q_2.*, T_A.PkID
  FROM Q_2, T_A
  WHERE (((T_A.PkID)>=[HoleStart] And (T_A.PkID)<=[HoleEnd])) ORDER BY
  Q_2.HoleStart, T_A.PkID; 
  ===================================

  Q_2  (Prelim Query - Stage 2)
  ===================================
  SELECT Q_1.Mark, 1+Min([PkID]) AS HoleStart, Max(Q_1.PkID) AS HoleEnd,
  Max([PkID])-Min([PkID]) AS HoleSpan FROM Q_1 GROUP BY Q_1.Mark HAVING
  (((Max([PkID])-Min([PkID]))>=1000));
  ===================================

  Q_1  (Prelim Query - Stage 1)
  ===================================
  SELECT T_A.PkID, Fn_Mark([T_B].[PkID]) AS Mark FROM T_A LEFT JOIN T_B ON
  T_A.PkID = T_B.PkID;
   ===================================

  ' Fn_Mark()
  ===================================
  ' Global Variable
  Public Mk As Variant
  '----------------------------------------------------

  Function Fn_Mark(PkVal As Variant) As Variant
      If Len(PkVal) > 0 Then
          Mk = PkVal
      End If
      
      Fn_Mark = Mk
  End Function
  ===================================

    ----- Original Message -----
    From: jwcolby
    To: 'Access Developers discussion and problem solving' 
    Sent: Saturday, March 01, 2008 19:35
    Subject: Re: [AccessD] Cnts in sets

    Before I launch into that you should understand that missing values are
  OK.
    Of the original 64 million records, roughly 13 million are missing, thus
    there are missing PKs throughout the data, but they are just "one here and
    there", in fact on average one every 8 records.  It is only "large holes"
  (I understand that is a rather nebulous term) that I care about.

    John W. Colby
    Colby Consulting
    www.ColbyConsulting.com

  <<Snipped>>