Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 31st, 2004, 11:11 AM
Authorized User
 
Join Date: Jun 2003
Posts: 71
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to rylemer Send a message via MSN to rylemer Send a message via Yahoo to rylemer
Default SELECT every fourth record

Hi! All this is my follow question in Splitting database.
I need to Select every fourth record in my database.

Appreciate all your help

Thanks,
Elmer
 
Old September 1st, 2004, 05:06 AM
Authorized User
 
Join Date: Aug 2004
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Elmer,

One way is to open a recordset object with VBA and iterate through the recordset. Every fourth record you grab the unique identifier and run an append query to another table.

Another way is to create an new empty table with the same structure as your original, but with the addition of an autonumber field (Named NewIdentifer for this example). Use an append query and send all the records into this new table. This will create a new list with a sequential record number for each record and with no omissions.

All you do now is create a select query that returns all the fields you need with a 'where clause' similar to 'WHERE Table1.NewIdentifer/4 = INT(Table1.NewIdentifer/4)'. Translation: when the value of the autonumber field can be divided by 4 and return a remainder of zero you have the fourth record.

* The where clause is essentially a substitute for the MOD function. It will work for all versions of Access. I'm not entirely certain but if you're using Access 2002 onwards and you have the 'Microsoft Office 10.0 Object Library' referenced then you should be able to use the MOD function eg 'MOD(NewIdentifier,4) = 0'

Anyhow have a play and see how you go.











Similar Threads
Thread Thread Starter Forum Replies Last Post
how to select the particular record? azymd777 ASP.NET 2.0 Professional 1 August 8th, 2008 05:55 AM
Select two MAX record per each veeruu SQL Language 2 January 23rd, 2006 12:57 AM
Select record after inserting it whyulil Classic ASP Databases 13 April 26th, 2004 06:05 AM
select last few record..... pls help life_s Ng ASP.NET 1.0 and 1.1 Basics 4 October 1st, 2003 01:36 AM
How to search for (and select) a record? Haroldd Access 2 June 30th, 2003 06:50 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.