[AccessD] Cnts in sets

    Apparently, you wish to display the missing PkID's where the hole size (consecutive missing records) 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 records)
===================================
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
    Debug.Print Mk
    
    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 
  -----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 8:26 AM
  To: Access Developers discussion and problem solving
  Subject: Re: [AccessD] Cnts in sets

  John,

      You might like to have a look at my sample db named
  TrackMissingAndDuplicates and see whether it could help. It is available at
  Rogers Access Library (other developers library). Link -
  http://www.rogersaccesslibrary.com/OtherLibraries.asp#Tejpal,A.D.
   
      Gist of holes (missing sequential values) is displayed, giving the start
  and end values of each hole. In the situation explained by you, before
  testing for holes, the source data can first be filtered so as to eliminate
  Null values.

      Note - This sample is for Access 2000 / 2003 desktop and the execution
  speed has not been tested in the context of huge tables of the size
  mentioned by you. However, if the underlying principle is found applicable,
  suitable adaptation could be considered, say by testing smaller chunks at a
  time in such a manner that there are always some common records between
  adjacent chunks (i.e the last record of a given sub-table is also the first
  record of next sub-table).
   
  Best wishes,
  A.D.Tejpal
  ------------

  <<Snipped>>