[AccessD] Select every nth record

That will work fine in SQL too, as long as the PKs are sequential.

Arthur


On 5/18/07, Mark A Matte <markamatte at hotmail.com> wrote:
>
> 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 them with 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
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>