By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,950 Members | 986 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,950 IT Pros & Developers. It's quick & easy.

Every n-th record in a selection

P: 8
WINDOWS XP, Access 2003
I have a query that lists certain amounts as follows

Select a,b,c, amount
from table
ordered by amount.

Now i've been asked to produce a list of every n-th record in that list (preferably in such a way as to be able to reproduce the selection identically to previous selections, also n should be entered manually).

Any suggestions as how to do this ?
May 11 '07 #1
Share this Question
Share on Google+
4 Replies


JConsulting
Expert 100+
P: 603
WINDOWS XP, Access 2003
I have a query that lists certain amounts as follows

Select a,b,c, amount
from table
ordered by amount.

Now i've been asked to produce a list of every n-th record in that list (preferably in such a way as to be able to reproduce the selection identically to previous selections, also n should be entered manually).

Any suggestions as how to do this ?
Expand|Select|Wrap|Line Numbers
  1. SELECT a,b,c, amount
  2. FROM Table
  3. WHERE ((([myindex]-1) Mod [enter Nth]=0));
  4.  
May 12 '07 #2

P: 8
Expand|Select|Wrap|Line Numbers
  1. SELECT a,b,c, amount
  2. FROM Table
  3. WHERE ((([myindex]-1) Mod [enter Nth]=0));
  4.  
Thank you for this solution.
My problem being that there is not really an index since the fields need to be ordered by a series of other fields (i failed to mention this in the initial question, a,b and c).

I gues I'll just have to create an index unless someone has another solution.
May 14 '07 #3

NeoPa
Expert Mod 15k+
P: 31,342
The only solution I can think of is to process through the recordset in code. Unfortunately, this cannot be used as the Record Source for objects like forms or reports.
Essentially, an RDBMS is designed to treat each record as a unique item and so the idea of ordinal numbers (1st; 2nd; 3rd; 4th; etc), or anything that is positionally relative is anathema (It doesn't make sense within that scenario).
Sometimes, tricks can be used to imitate what's required, but I can't see a useful one here.
May 17 '07 #4

JConsulting
Expert 100+
P: 603
The only solution I can think of is to process through the recordset in code. Unfortunately, this cannot be used as the Record Source for objects like forms or reports.
Essentially, an RDBMS is designed to treat each record as a unique item and so the idea of ordinal numbers (1st; 2nd; 3rd; 4th; etc), or anything that is positionally relative is anathema (It doesn't make sense within that scenario).
Sometimes, tricks can be used to imitate what's required, but I can't see a useful one here.
>>The only solution I can think of is to process through the recordset in code

If a temp table was used to store every nth record while cycling through a recordset, then that could be used as the source for a form or report.

J
May 17 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.