[AccessD] Select every nth record

Hi John

Extending Mark's idea, add the primary keys of the client's records to a selection table of yours which also has a sequential autonumber and a field for selected.
When records are "drawn", update field Selected to True.
Now, when new records arrive, append these to your table and then select every second record by joining the client's table with yours where Selected = False and your autonumber Id Mod 2 = 0 and (perhaps) where Id > the largest Id of the last run.

/gustav

>>> jwcolby at colbyconsulting.com 18-05-2007 17:53 >>>
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