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

Returning the nth record from a client details table

P: 10

Using MS Access 2003, I have a list client details (firstname, middlename, surname, address1, etc).

I need to generate a new client list that is made up of the 8th record from the original list.

I also need to remove the 8th record from the original list.

So, I should finish with 2 lists

Every 8th record client list
Every other record

The client list contains 40000 records.

What would be the easiest way to do this?

Thanks in advance.
Jul 19 '07 #1
Share this Question
Share on Google+
5 Replies

Expert 2.5K+
P: 3,072
Start with adding an autonumber ID field.
Now create a query like:

Expand|Select|Wrap|Line Numbers
  1. SELECT Tabel1.ID, [id] Mod 8 AS Expr1
  2. FROM Tabel1
  3. WHERE ((([id] Mod 8)=0));
This will select every 8th row and can be changed into a maketable query.

Finally use this newly created table with an OUTER JOIN to construct another maketable query to select the "other" records.

Getting the idea ?

Jul 19 '07 #2

P: 10
Thanks Nic, I've got the list containing every 8th row, your code made it so simple.

I think I understand the concept of an OUTER JOIN, in my situation I believe I need a RIGHT OUTER JOIN. I'm using postcode as my joined field but Igetting more records than I orginally started with. Could you point out where I'm going wrong?

Expand|Select|Wrap|Line Numbers
  1. SELECT table1.*
  2. FROM table1 RIGHT JOIN table2 ON table1.Postcode = table2.Postcode;
Jul 20 '07 #3

Expert 2.5K+
P: 3,072
You'll need the added ID (autonumber) field for the JOIN to keep the rows "in sync".
Also add for the filtering of the not used (not 8th) rows one field from the 8th row table and add the criteria: NOT IS NULL
(Make sure Access doesn't wrap the NOT IS NULL text in quotes ("), else it's a string compare....)

When the correct rows are selected you can change the query into a maketable query to get the second table.


Jul 20 '07 #4

P: 10
I was being a bit thick :o)

I used the same query created to show every 8th client and changed this to a deletetable query.

This removed my sample survey list from the original list.

Many Thanks for help.
Jul 20 '07 #5

Expert 2.5K+
P: 3,072
I prefer to use "non-destructive "maketable" queries, as that's allowing me to make mistakes and try again :-)

Success !

Jul 20 '07 #6

Post your reply

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