Query Syntax to get 1st 100 records Then 2nd 100 Etc, Etc

[Frank Lueder]

How can I get a query to get first 100 records of a 1000 record table, then the second 100 and so on to get 10 equal lists. I want
the list to be sorted on the Hobbies field prior to doing the queries.
Any help would sure be appreciated.
Thanks, Frank

 

[Duane Hookom]

There is no such thing as "First" in an Access table. There may be First and
Last in a query that has a particular sort order set. Perhaps you could
describe your table and what you mean by "first 100"?

 

[Randy Balbuena]

How can I get a query to get first 100 records of a 1000 record table,
then the second 100 and so on to get 10 equal lists. I want
the list to be sorted on the Hobbies field prior to doing the queries.
Any help would sure be appreciated.
Thanks, Frank

Frank,

You should explain to us a little bit more of what you are trying to do or
accomplish. However let me approach with a guess: There is a TOP clause in
SQL language that allows you to returns a certain number of records that
fall at the top or the bottom of a certain range. The range is specified by
an ORDER BY clause. Suppose you want the names of the "Top 10 Richest People
in America:"

SELECT TOP 10
PersonsName, FortuneInMillions
FROM tblWorldsReachestPeople
WHERE Country = "United States"
ORDER BY FortuneInMillions DESC;

[Luckily my name will rank on this category ;-)

You can also use the PERCENT reserved word to return a certain percentage of
records that fall at the top or the bottom of a range specified. Ex.: SELECT
TOP 10 PERCENT [fields.......

Alternatively, you could use the "TopValues" property of a query to achieve
the same result. Note that the "TopValues" property applies only to Append,
Make-table, and Select queries.

Hope that helps.

 

[Marshall Barton]

How can I get a query to get first 100 records of a 1000 record table, then the second 100 and so on to get 10 equal lists. I want
the list to be sorted on the Hobbies field prior to doing the queries.

Once you've figured out what "first" means in terms that can
be expressed in the a query's ORDER BY clause, you can then
use:

top 100:
SELECT TOP 100 *
FROM table
ORDER BY somefield

second 100:
SELECT TOP 100 *
FROM table
WHERE Not somefield IN(SELECT TOP 100 *
FROM table
ORDER BY somefield)
ORDER BY somefield

third 100:
SELECT TOP 100 *
FROM table
WHERE Not somefield IN(SELECT TOP 200 *
FROM table
ORDER BY somefield)
ORDER BY somefield

etc.

 

[Frank Lueder]

I'm not sure if should reply to the group or to individuals, but I hope the following explains what I need a little bit better.

I want to let you know up front that I don't know that much about using SQL or Access. I was nominated as secretary of our Naval
Squadron Association (now decommissioned). Most all of the members are no longer in the military, but we are trying to keep them
all informed of what's happening with the association and keeping their contact information up-to-date for reunion purposes. As
secretary I have to do that. I used the easiest method I could think of and used the existing address data base in Access 2000. At
our last reunion we assigned 10 members to overlook and keep in contact with their ERA. We wanted to break the total members into
10 Groups. Easier for me and not having to contact all 1175 members. Each group leader would be responsible for keeping their
assigned individuals up to date.

The ERA Groups are:
ERA Grp 1 Years 1946-51
ERA Grp 2 Years 1952-56
ERA Grp 3 Years 1957-58
ERA Grp 4 Years 1959-60
ERA Grp 5 Years 1961-62
ERA Grp 6 Years 1963-65
ERA Grp 7 Years 1966-69
ERA Grp 8 Years 1970-72
ERA Grp 9 Years 1973-83
ERA Grp 10 Years 1984-93

Total members at present time is 1175. I am using the "Members Table" & the "Household Table". I'm not sure on how to copy them so
you can see them other than type them individually. If you need them let me know. In the "Members Table" there is a field called
"Hobbies". I used this field to put the dates each individual was in the squadron just like it is shown above. I just change the
name on the report so it displays "Yr in Squad". There are 175 individuals that we don't have the years in squadron listed. I was
able set a query criteria "is null" for "hobbies" and get all members listed without years in squadron. I would like to break that
down into 10 groups as easily as possible, and then add each of these to the ERA Groups, so each Group Leader will have about the
same amount of members. If you need any other information please let me know.
Here's hoping you will be able to help figure this out.
Thanks, Frank