Selecting every 4th record

[mleech]

Hi,

I have a table with over 90,000 records, how do I create a query to select only every 4th record? If this cannot be done with a query, what are my options?

Thanks

 

[Newbie100]

The easiest way is to add an AutoNumber field in your table e.g. ID and run a query like the following:

SELECT *
FROM [TableName]
WHERE [ID] Mod 4 =0;


If the table already has an AutoNumber field, you can copy the table to a temporary table, change the AutoNumber field in the temp table to Number, Long Integer, and add an AutoNumber field. Then run the query off of this temp table.

 

[ajetrumpet]

another useful place...

http://support.microsoft.com/kb/207651/en-us

 

[KeithG]

Records in a table do not actually have an order. There is no first record or second record. For example the first record would be different if you sorted the recordset ascending and then descending. It would be a violation of the relational model if records in a table required an order.

 

[Newbie100]

May need to add this:-

If you want to retrieve every 4th records from a particular order, you need to sort the records in that order in a make-table query to create a temporary table. Then add the autonumber ID field in the temp table and run the query with Where [ID] mod 4 = 0

The basic principle is to number the records in the ID field with 1,2,3,4,5,.... etc
before applying ID mod 4 = 0

 

[mleech]

Thank you for your help, however I was able to add the auto number field, but not sure what kind of query to run. I thought it might be a make table query or a select query, but I am not sure where to put the WHERE [ID] mod 4=0. There is no options that you are able to put in your SELECT * FROM or WHERE

thanks

 

[Jon K]

It's a select query. You can switch the query to SQL view and type the SQL statement there, using the correct table name and field name.

SELECT *
FROM [TableName]
WHERE [AutoNumberFieldName] Mod 4 =0;
.