[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 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>>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com