[AccessD] Cnts in sets

Hello John,

Just curious whether that worked for you, 65 Million records is a lot and I
have recently learned how tedious it is to wait for Order by etc.

Mark



On 01/03/2008, jwcolby <jwcolby at colbyconsulting.com> wrote:
>
> 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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>