finding every 7th record in a query

[Guest]

Hi, I have a table or names for a mailshot, with a sequentially ordered
Autonumber field (LeisureID). If I put that in my query with the name fields,
what do I need to enter into the LeisureID filed to return every 7th record?I
know this is possible but, don't know how and I'm not that au fait with
expressions and functions. Can anyone give me a noddy's guide please? Many
thanks.

 

[Guest]

If you are sure you have all sequential numbers in the LeisureID field then
you can use something like:
SELECT [names for a mailshot].*
FROM [names for a mailshot]
WHERE [LeisureID] Mod 7=1
ORDER BY LeisureID;

 

[Guest]

Hi, thanks for your prompt reply but I don't understand what to put where.
As I said, I need a noddy's guide... I have in my query the fields
LeisureID, Salutation, First Name, Second Name and address stuff. What
exactly do I need to put in the Criteria field below LeisureID? Sorry to be a
dunce.
--
Bridget

If you are sure you have all sequential numbers in the LeisureID field then
you can use something like:
SELECT [names for a mailshot].*
FROM [names for a mailshot]
WHERE [LeisureID] Mod 7=1
ORDER BY LeisureID;

--
Duane Hookom
Microsoft Access MVP

Hi, I have a table or names for a mailshot, with a sequentially ordered
Autonumber field (LeisureID). If I put that in my query with the name fields,
what do I need to enter into the LeisureID filed to return every 7th record?I
know this is possible but, don't know how and I'm not that au fait with
expressions and functions. Can anyone give me a noddy's guide please? Many
thanks.

 

[raskew via AccessMonster.com]

Duane -

Nice solution!!

Best wishes - Bob

Hi, thanks for your prompt reply but I don't understand what to put where.
As I said, I need a noddy's guide... I have in my query the fields
LeisureID, Salutation, First Name, Second Name and address stuff. What
exactly do I need to put in the Criteria field below LeisureID? Sorry to be a
dunce.

If you are sure you have all sequential numbers in the LeisureID field then
you can use something like:

[quoted text clipped - 9 lines]

 

[Guest]

Bridget,

Create a new field in your query, doesn't matter what you call it as you
will not be displaying it.

Field: [LeisureID] mod 7
Show: uncheck this box
Criteria: 7

As Duane indicated, this will only actually work (every 7th record) if the
records are in fact sequential. If you have deleted records, then this will
not be exactly every 7th record.

If it is imperative that you have every 7th, or 5th, or nth record, then
post back for a more complicated solution.


--
Email address is not valid.
Please reply to newsgroup only.

Hi, thanks for your prompt reply but I don't understand what to put where.
As I said, I need a noddy's guide... I have in my query the fields
LeisureID, Salutation, First Name, Second Name and address stuff. What
exactly do I need to put in the Criteria field below LeisureID? Sorry to be a
dunce.
--
Bridget

If you are sure you have all sequential numbers in the LeisureID field then
you can use something like:
SELECT [names for a mailshot].*
FROM [names for a mailshot]
WHERE [LeisureID] Mod 7=1
ORDER BY LeisureID;

--
Duane Hookom
Microsoft Access MVP

Hi, I have a table or names for a mailshot, with a sequentially ordered
Autonumber field (LeisureID). If I put that in my query with the name fields,
what do I need to enter into the LeisureID filed to return every 7th record?I
know this is possible but, don't know how and I'm not that au fait with
expressions and functions. Can anyone give me a noddy's guide please? Many
thanks.

 

[Guest]

Hello Dale,
Thanks for that: I've added a new field and put in the following:
Selection: [LeisureID] Mod 7 (in the field space)
Then put 7 in the Criteria space.
And unchecked the 'show' box

But when I run the query, nothing comes up... (when I tried Duan's
suggestion I got an error message, I must have been doing something
incorrectly).

What am I still doing wrong?

I have a database of 7000 names and only 1000 flyers, so need to extract
every 7th approximately for a mailshot. I would be interested in finding out
what to do with a more complicated solution if you have the time to reply.
Thanks
--
Bridget

Bridget,

Create a new field in your query, doesn't matter what you call it as you
will not be displaying it.

Field: [LeisureID] mod 7
Show: uncheck this box
Criteria: 7

As Duane indicated, this will only actually work (every 7th record) if the
records are in fact sequential. If you have deleted records, then this will
not be exactly every 7th record.

If it is imperative that you have every 7th, or 5th, or nth record, then
post back for a more complicated solution.


--
Email address is not valid.
Please reply to newsgroup only.

Hi, thanks for your prompt reply but I don't understand what to put where.
As I said, I need a noddy's guide... I have in my query the fields
LeisureID, Salutation, First Name, Second Name and address stuff. What
exactly do I need to put in the Criteria field below LeisureID? Sorry to be a
dunce.
--
Bridget

If you are sure you have all sequential numbers in the LeisureID field then
you can use something like:
SELECT [names for a mailshot].*
FROM [names for a mailshot]
WHERE [LeisureID] Mod 7=1
ORDER BY LeisureID;

--
Duane Hookom
Microsoft Access MVP


:

Hi, I have a table or names for a mailshot, with a sequentially ordered
Autonumber field (LeisureID). If I put that in my query with the name fields,
what do I need to enter into the LeisureID filed to return every 7th record?I
know this is possible but, don't know how and I'm not that au fait with
expressions and functions. Can anyone give me a noddy's guide please? Many
thanks.

 

[John Spencer]

Pardon me for jumping in. You will need to add a calculated field.

Field: Every7: [Your Table Name].[LeisureID] Mod 7
Criteria: 1

You can use any number from 0 to 6 to get every seventh record.

If LeisureID is sequential and without breaks this will work to give you
records 1, 8 , 15, ...
If LeisureID has gaps in it you will still get the records that are
divisible by 7 with a remainder of 1 but that may not be every seventh
record.

If you need to get every seventh record exactly, then post back

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi, thanks for your prompt reply but I don't understand what to put
where.
As I said, I need a noddy's guide... I have in my query the fields
LeisureID, Salutation, First Name, Second Name and address stuff. What
exactly do I need to put in the Criteria field below LeisureID? Sorry to
be a
dunce.
--
Bridget

If you are sure you have all sequential numbers in the LeisureID field
then
you can use something like:
SELECT [names for a mailshot].*
FROM [names for a mailshot]
WHERE [LeisureID] Mod 7=1
ORDER BY LeisureID;

--
Duane Hookom
Microsoft Access MVP

Hi, I have a table or names for a mailshot, with a sequentially
ordered
Autonumber field (LeisureID). If I put that in my query with the name
fields,
what do I need to enter into the LeisureID filed to return every 7th
record?I
know this is possible but, don't know how and I'm not that au fait with
expressions and functions. Can anyone give me a noddy's guide please?
Many
thanks.

 

[John Spencer]

The problem is that MOD 7 only returns values from 0 to 6 so the criteria
should be a number from 0 to 6.

0 will return records that are exactly divisible by 7 (LeisureID = 7, 14,
21, 28, ...)
1 yields records (1,8,15,22, 29, ...)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hello Dale,
Thanks for that: I've added a new field and put in the following:
Selection: [LeisureID] Mod 7 (in the field space)
Then put 7 in the Criteria space.
And unchecked the 'show' box

But when I run the query, nothing comes up... (when I tried Duan's
suggestion I got an error message, I must have been doing something
incorrectly).

What am I still doing wrong?

I have a database of 7000 names and only 1000 flyers, so need to extract
every 7th approximately for a mailshot. I would be interested in finding
out
what to do with a more complicated solution if you have the time to reply.
Thanks
--
Bridget

Bridget,

Create a new field in your query, doesn't matter what you call it as you
will not be displaying it.

Field: [LeisureID] mod 7
Show: uncheck this box
Criteria: 7

As Duane indicated, this will only actually work (every 7th record) if
the
records are in fact sequential. If you have deleted records, then this
will
not be exactly every 7th record.

If it is imperative that you have every 7th, or 5th, or nth record, then
post back for a more complicated solution.


--
Email address is not valid.
Please reply to newsgroup only.

Hi, thanks for your prompt reply but I don't understand what to put
where.
As I said, I need a noddy's guide... I have in my query the fields
LeisureID, Salutation, First Name, Second Name and address stuff. What
exactly do I need to put in the Criteria field below LeisureID? Sorry
to be a
dunce.
--
Bridget


:

If you are sure you have all sequential numbers in the LeisureID
field then
you can use something like:
SELECT [names for a mailshot].*
FROM [names for a mailshot]
WHERE [LeisureID] Mod 7=1
ORDER BY LeisureID;

--
Duane Hookom
Microsoft Access MVP


:

Hi, I have a table or names for a mailshot, with a sequentially
ordered
Autonumber field (LeisureID). If I put that in my query with the
name fields,
what do I need to enter into the LeisureID filed to return every
7th record?I
know this is possible but, don't know how and I'm not that au fait
with
expressions and functions. Can anyone give me a noddy's guide
please? Many
thanks.

 

[Guest]

Sorry about that, hadn't finished my first cup of coffee yet.

--
Email address is not valid.
Please reply to newsgroup only.

Hello Dale,
Thanks for that: I've added a new field and put in the following:
Selection: [LeisureID] Mod 7 (in the field space)
Then put 7 in the Criteria space.
And unchecked the 'show' box

But when I run the query, nothing comes up... (when I tried Duan's
suggestion I got an error message, I must have been doing something
incorrectly).

What am I still doing wrong?

I have a database of 7000 names and only 1000 flyers, so need to extract
every 7th approximately for a mailshot. I would be interested in finding out
what to do with a more complicated solution if you have the time to reply.
Thanks
--
Bridget

Bridget,

Create a new field in your query, doesn't matter what you call it as you
will not be displaying it.

Field: [LeisureID] mod 7
Show: uncheck this box
Criteria: 7

As Duane indicated, this will only actually work (every 7th record) if the
records are in fact sequential. If you have deleted records, then this will
not be exactly every 7th record.

If it is imperative that you have every 7th, or 5th, or nth record, then
post back for a more complicated solution.


--
Email address is not valid.
Please reply to newsgroup only.

Hi, thanks for your prompt reply but I don't understand what to put where.
As I said, I need a noddy's guide... I have in my query the fields
LeisureID, Salutation, First Name, Second Name and address stuff. What
exactly do I need to put in the Criteria field below LeisureID? Sorry to be a
dunce.
--
Bridget


:

If you are sure you have all sequential numbers in the LeisureID field then
you can use something like:
SELECT [names for a mailshot].*
FROM [names for a mailshot]
WHERE [LeisureID] Mod 7=1
ORDER BY LeisureID;

--
Duane Hookom
Microsoft Access MVP


:

Hi, I have a table or names for a mailshot, with a sequentially ordered
Autonumber field (LeisureID). If I put that in my query with the name fields,
what do I need to enter into the LeisureID filed to return every 7th record?I
know this is possible but, don't know how and I'm not that au fait with
expressions and functions. Can anyone give me a noddy's guide please? Many
thanks.

 

[Guest]

Hello John.

Thanks for that - it now works a dream. Thank you to the other contributors
for their patience.
--
Bridget

Pardon me for jumping in. You will need to add a calculated field.

Field: Every7: [Your Table Name].[LeisureID] Mod 7
Criteria: 1

You can use any number from 0 to 6 to get every seventh record.

If LeisureID is sequential and without breaks this will work to give you
records 1, 8 , 15, ...
If LeisureID has gaps in it you will still get the records that are
divisible by 7 with a remainder of 1 but that may not be every seventh
record.

If you need to get every seventh record exactly, then post back

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi, thanks for your prompt reply but I don't understand what to put
where.
As I said, I need a noddy's guide... I have in my query the fields
LeisureID, Salutation, First Name, Second Name and address stuff. What
exactly do I need to put in the Criteria field below LeisureID? Sorry to
be a
dunce.
--
Bridget

If you are sure you have all sequential numbers in the LeisureID field
then
you can use something like:
SELECT [names for a mailshot].*
FROM [names for a mailshot]
WHERE [LeisureID] Mod 7=1
ORDER BY LeisureID;

--
Duane Hookom
Microsoft Access MVP


:

Hi, I have a table or names for a mailshot, with a sequentially
ordered
Autonumber field (LeisureID). If I put that in my query with the name
fields,
what do I need to enter into the LeisureID filed to return every 7th
record?I
know this is possible but, don't know how and I'm not that au fait with
expressions and functions. Can anyone give me a noddy's guide please?
Many
thanks.

 

[iliace]

Hello John.

Thanks for that - it now works a dream. Thank you to the other contributors
for their patience.
--
Bridget

Pardon me for jumping in. You will need to add a calculated field.

Field: Every7: [Your Table Name].[LeisureID] Mod 7
Criteria: 1

You can use any number from 0 to 6 to get every seventh record.

If LeisureID is sequential and without breaks this will work to give you
records 1, 8 , 15, ...
If LeisureID has gaps in it you will still get the records that are
divisible by 7 with a remainder of 1 but that may not be every seventh
record.

If you need to get every seventh record exactly, then post back

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi, thanks for your prompt reply but I don't understand what to put
where.
As I said, I need a noddy's guide... I have in my query the fields
LeisureID, Salutation, First Name, Second Name and address stuff. What
exactly do I need to put in the Criteria field below LeisureID? Sorry to
be a
dunce.
--
Bridget
:
If you are sure you have all sequential numbers in the LeisureID field
then
you can use something like:
SELECT [names for a mailshot].*
FROM [names for a mailshot]
WHERE [LeisureID] Mod 7=1
ORDER BY LeisureID;
--
Duane Hookom
Microsoft Access MVP
:
Hi, I have a table or names for a mailshot, with a sequentially
ordered
Autonumber field (LeisureID). If I put that in my query with the name
fields,
what do I need to enter into the LeisureID filed to return every 7th
record?I
know this is possible but, don't know how and I'm not that au fait with
expressions and functions. Can anyone give me a noddy's guide please?
Many
thanks.

- Show quoted text -

This might be a belated response, but - without sorting the inital
fields which you're counting - what is the purpose of selecting "every
seventh field?" Couldn't you just select the first seven fields you
encounter? Correct me if I'm out of line here.

If you're going for a sample, for the selection criteria it seems
absolutely necessary to include sort fields. Otherwise, your entire
excerise serves no purpose.

For instance, if you had a list of employees and you sorted them by
zipcode and took every 7th... that would be a better sample than doing
the same with a query sorted alphabetically by last name. You can
probably imagine scenarios where this is even more relevant.

To consider your end-result requirements first, and then to post a
more specific inquiry as to how to achieve these, would be my
suggestion. But, if you already have what works well enough for you,
there might not be a need to bother.