jwcolby
jwcolby at colbyconsulting.com
Fri May 18 12:24:05 CDT 2007
That kind of thing works the first time but will it work the second time? You take out the 4th and 8th record leaving 1,2,3,5,6,7,9 Not using a mod on those will not leave no remainder because all the ones evenly divisible by 4 are gone. 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 1:09 PM To: accessd at databaseadvisors.com Subject: Re: [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_mig >ratio >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 storageget 2GB with Windows Live Hotmail. http://imagine-windowslive.com/hotmail/?locale=en-us&ocid=TXT_TAGHM_migratio n_HM_mini_2G_0507