Retriving Record "x" (1 Viewer)

[rmarquez5]

Greetings All.

I have one query that retrieves a list of all movie titles whose ID is between A and B. Currently, this query retrieves exactly 525 records.

I want to write another query that retrieves every 75th record from the above. So want record 75, record 150, record 225, etc.

Is there a way to do this? I can't seem to find a function to do this. Do I have to write it into a procedure? Use a loop or something??

Any input you can provide would be most helpful.
Thanks a bunch. I hope to hear from you.

 

[WayneRyan]

R.

Every 75th what? Are they in order by date, title, actors?

The TOP predicate can retrieve n number of rows in a query,
but I think you need an example similar to selecting random
questions for a test. It uses a recordset to loop through and
pick out some random ones.

I think Mile posted one a while ago. Use the Search Facility
here and look for "Random and Test".

Wayne

 

[rmarquez5]

Hi Wayne.

The first query is ordered by title. I need every 75th title. I'll try "TOP" to see what I can generate though. Thanks.

 

[WayneRyan]

R,

Here's some light reading ...

http://www.access-programmers.co.uk...d=283171&sortby=lastpost&sortorder=descending

Wayne

 

[Mile-O]

If you have an Autonumber field then I'd just create a new field in the query.

i.e.

NewField: [MyField] Mod 75

and set its criteria to: 0

 

[WayneRyan]

Nice Mile!

Wayne

 

[rmarquez5]

That's a great idea... but my AutoNumber field has gaps in the numbers. Is there any way to renumber an AutoNumber field??

 

[WayneRyan]

R,

You should not count on an Autonumber to provide anything
except a unique identifier for each record. If you need to
you can see the link above to generate "random" records.

Do you really need an exact number of records? The MOD
function in a query really looks like the way to go.

Wayne

 

[rmarquez5]

Thanks for all the input, peoples.

The MOD function was my saving grace. I ended up making another table with an AutoNumber field and movie title field, inserting the records into that table, and running a query to find all ID MOD 75 = 0. Works like a charm.

Once again, the Access World Forums saves the day. Thanks again.