[AccessD] Every 100th record

A.D.Tejpal adtp at touchtelindia.net
Fri Aug 27 13:04:49 CDT 2004


    If there are gaps in the sequential values pertaining to the primary key field, the following could be considered (T_Data is the name of table and ID is the name of primary key).

SELECT T_Data.*
FROM T_Data
WHERE (Select Count(*) From T_Data T1 Where T1.ID <=  T_Data.ID) Mod 100 = 0;

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

  ----- Original Message ----- 
  From: Heenan, Lambert 
  To: 'Access Developers discussion and problem solving' ; 'John W. Colby' 
  Sent: Friday, August 27, 2004 21:58
  Subject: RE: [AccessD] Every 100th record


  If this table has a sequentially numbered Identity field (if that's the
  correct name for the SQL version of an AutoNumber) then just run a select
  query with the condition (Mod 100 = 0) on the Identity field.

  Lambert

  > -----Original Message-----
  > From: accessd-bounces at databaseadvisors.com
  > [SMTP:accessd-bounces at databaseadvisors.com] On Behalf Of John W. Colby
  > Sent: Friday, August 27, 2004 11:39 AM
  > To: 'Access Developers discussion and problem solving'
  > Subject: RE: [AccessD] Every 100th record
  > 
  > Gustav,
  > 
  > I am working on a SQL Server database of about 65 million records.  We
  > need
  > to pull a subset of those for doing counts of data in specific fields.
  > Trying to do that analysis on the entire 65 million records just won't
  > work
  > at least in anything close to realtime.  Thus we literally want to pull
  > every Nth record.  If we pulled every 100th record into a table that would
  > give a sampling of 650K records to run this analysis on.  That still won't
  > be lightning fast but at least doable.
  > 
  > John W. Colby
  > www.ColbyConsulting.com 
  > 
  > -----Original Message-----
  > From: accessd-bounces at databaseadvisors.com
  > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
  > Sent: Friday, August 27, 2004 11:22 AM
  > To: Access Developers discussion and problem solving
  > Subject: Re: [AccessD] Every 100th record
  > 
  > 
  > Hi John
  > 
  > 
  > > Does anyone have a strategy for pulling every Nth record?  My client 
  > > wants to pull every 100th record into a dataset for analysis, to speed 
  > > things up I am guessing.
  > 
  > To speed up what? Analysis on a sample only and not on the full set?
  > 
  > If so, you could select by "Random Between 1 To 100" = 1.
  > 
  > /gustav




More information about the AccessD mailing list