[AccessD] Select every nth record

John,

If I were going to attempt this in SQL Server...I would probably use a 
cursor in query analyzer...determine if the current record count is 
divisable by your specified nth number...if so call an SP to write that 
record.

In access I would use the function below to get a sequential record 
number...and "[record Number] MOD nth" in the query and filter for 0.  This 
would get you every nth record.

Example Every 4th record upto 1000 cases
********SQL*******
SELECT TOP 1000 tblPurvi.case_id, tblPurvi.case_type INTO tblTest
FROM tblPurvi
WHERE (((MyAutoCtr([case_id]) Mod 4)=0));
*******************

I had 8000 records in tblPurvi...I ran this...gave me 1000 records...I 
joined tblText back to tblPurvi...and showed that it in fact chose every 4th 
record.  MyAutoCtr is the only non native function...and if you have a PK 
that is sequential...you don't even need MyAutoCtr...just the filtered MOD 
nth.

Good luck,

Mark A. Matte


>From: "jwcolby" <jwcolby at colbyconsulting.com>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: "'Access Developers discussion and problem 
>solving'"<accessd at databaseadvisors.com>
>Subject: Re: [AccessD] Select every nth record
>Date: Fri, 18 May 2007 11:53:14 -0400
>
>Thanks for the suggestion.  I am trying to figure out something generic
>here.  What often happens (not in this case) is my client says "give me a
>count where...".  When the count comes up as 523,417 they will then say,
>give me every nth record in order to select 200,000 records.  I have to
>track the exact records selected since they may come back later and say,
>every Nth record for another 200,000 NOT IN the first 200,000.  Etc.
>
>I do not know how to even approach such a problem with SQL alone.  The
>"exactly 200K records" is the killer for me.  I think I can use a TOP
>(200000) ORDER BY NEWID(), but I still have to track the "already used" -
>probably a simple SELECTED field.
>
>In THIS SPECIFIC CASE, the client said "every other record" which has a
>specific meaning.  I am asking him if he really means that or not.
>
>
>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 Mark A Matte
>Sent: Friday, May 18, 2007 11:36 AM
>To: accessd at databaseadvisors.com
>Subject: Re: [AccessD] Select every nth record
>
>John,
>
>I do something similar to get duplex printing of postcards.  There is a
>sample db on the DBA website under the 2002 conference page.
>
>Basically I create an autonumber in a query using the function MyAutoCtr()
>...(module below)...Then I filter out the even number records using the MOD
>function.
>
>Hope that helps...let me know if you have any questions.
>
>Thanks,
>
>Mark A. Matte
>
>
>************Module***************
>Global lngTableCounter As Long
>
>Function MyAutoCtr(prmAny)
>'Trick is to pass a field from the input table(s) so that function called
>for each record 'otherwise Access thinks that the function will always
>return the same value and 'only calls it once, and every output record gets
>the same value MyAutoCtr = lngTableCounter lngTableCounter = 
>lngTableCounter
>+ 1 End Function
>************Module***************
>
>
> >From: "jwcolby" <jwcolby at colbyconsulting.com>
> >Reply-To: Access Developers discussion and problem
> >solving<accessd at databaseadvisors.com>
> >To: "'Access Developers discussion and problem
> >solving'"<accessd at databaseadvisors.com>
> >Subject: [AccessD] Select every nth record
> >Date: Fri, 18 May 2007 11:06:18 -0400
> >
> >Is there a SQL statement that will do this directly?  I have a table of
> >40K records and I want to split it into two tables, each containing
> >every other record of the 40K record table.
> >
> >John W. Colby
> >Colby Consulting
> >www.ColbyConsulting.com
> >
> >--
> >AccessD mailing list
> >AccessD at databaseadvisors.com
> >http://databaseadvisors.com/mailman/listinfo/accessd
> >Website: http://www.databaseadvisors.com
>
>_________________________________________________________________
>Catch suspicious messages before you open themwith Windows Live Hotmail.
>http://imagine-windowslive.com/hotmail/?locale=en-us&ocid=TXT_TAGHM_migratio
>n_HM_mini_protection_0507
>
>
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com

_________________________________________________________________
More photos, more messages, more storage—get 2GB with Windows Live Hotmail. 
http://imagine-windowslive.com/hotmail/?locale=en-us&ocid=TXT_TAGHM_migration_HM_mini_2G_0507