Pull every Nth record based on starting value

[LisaInAz]

I am using Access 2002. I have a sorted table and I used the DynamicCounter
logic to number the rows 1 through end of file, as I can not use another PK.
I need to query the table starting from a row number the customer will
supply and then pull every Nth record also supplied. Row number could be
722, 1545, etc and Nth could be 10, 21, 2203 etc.

I found another post with the below suggestion which almost works however it
appears that it is not starting from the specific row number nor picking Nth
till end of file.
--
Create a table CountNumber with field CountNUM containing numbers from 0
(zero) through your maximum sequence length. Substitute your table and field
name for [Change Requests].Primary_Key and enter interval and sequence at
prompts.
SELECT [Change Requests].Primary_Key
FROM [Change Requests], CountNumber
WHERE ((([Change Requests].Primary_Key)=([CountNUM]*[Enter interval])+[Enter
sequence]));
--

My tables do contain duplicates which is fine. My tables are named and
constructed as follows:
Tbl1 - Change Requests
fld1 - Number (Long Integer)
fld2 - Text Field

Tbl2 - CounterNumber
fld - CountNUM (Long Integer)
fld2 - Number

Any Suggestions or other Posts to Consider are greatly appreciated.

 

[Lord Kelvan]

well if your primary key a auto num and are you wanting is n = 10
starting from 10 to see record 10 20 30 40 50 what if record 30 is
deleted do you want to see 10 20 31 41 51 etc

regards
Kelvan

 

[Lord Kelvan]

tbh that counternum table is useless

use

SELECT [Change Requests].Primary_Key, [Change Requests].textfield
FROM [Change Requests]
WHERE ((([Change Requests].Primary_Key)>=[enter starting point]) AND
((([Primary_Key]-[enter starting point]) Mod [interval])=0))
ORDER BY [Change Requests].Primary_Key;

that will take a starting point value and an interval and give you the
values startign from the starting point

of course that will not show you values that have been deleted

so if you enter 10 for starting poin and 10 for interval and 30 has
been deleted then you will get
10
20
40
50
etc

this one though more complicated will do it based on the number of
records not the primary key value just incase of deletion

SELECT [Change Requests].Primary_Key, [Change Requests].textfield
FROM [Change Requests]
WHERE ((((select count(primary_key)+1 from [Change Requests] as subq
where subq.primary_key < [Change Requests].primary_key))>=CInt([enter
starting point])) AND ((((select count(primary_key)+1 from [Change
Requests] as subq where subq.primary_key < [Change
Requests].primary_key)-CInt([enter starting point])) Mod
[interval])=0))
ORDER BY [Change Requests].Primary_Key;

so in other words if record 10 and 30 were deleted and you entered 10
for start and 10 for interval your results would be

11
21
32
42
52
62
etc

i hope this helps

if you need any explication on what i haev done feel free to ask

Regards
Kelvan

 

[LisaInAz]

Thank you - Kevlan

Sorry it took me so long to get back, I used the following suggested code.
It appears to be working perfectly.

SELECT [Change Requests].Primary_Key, [Change Requests].textfield
FROM [Change Requests]
WHERE ((([Change Requests].Primary_Key)>=[enter starting point]) AND
((([Primary_Key]-[enter starting point]) Mod [interval])=0))
ORDER BY [Change Requests].Primary_Key;

I would like to understand the following, I was getting a syntax error when
I tried this. Could you please give me some insight as to what it is doing.
I can sort of follow along but get lost. Thanks
SELECT [Change Requests].Primary_Key, [Change Requests].textfield
FROM [Change Requests]
WHERE ((((select count(primary_key)+1 from [Change Requests] as subq
where subq.primary_key < [Change Requests].primary_key))>=CInt([enter
starting point])) AND ((((select count(primary_key)+1 from [Change
Requests] as subq where subq.primary_key < [Change
Requests].primary_key)-CInt([enter starting point])) Mod
[interval])=0))
ORDER BY [Change Requests].Primary_Key;

 

[Lord Kelvan]

whats the syntax error what is highlighted or what is said when the
error appears

i only ask because that sql is taked straight form a running query in
my ms access

Regards
Kelvan

 

[LisaInAz]

Sorry - I had a ) in the wrong place - Thanks again for your help