Selecting records by rowID

I've got a table of 11,000 records. Don't ask why, it's a long story, but I
need 11 queries like this:

Query 1: records 1 - 1000
Query 2: records 1001 - 2000
Query 3: records 2001 - 2000

and so on. Any way to do this?

Query 3: records 2001 - 3000

sorry


"Kirk P." wrote:

I've got a table of 11,000 records. Don't ask why, it's a long story, but I
need 11 queries like this:

Query 1: records 1 - 1000
Query 2: records 1001 - 2000
Query 3: records 2001 - 2000

and so on. Any way to do this?

Access doesn't have a RowID. Do you have a field, perferrably a primary key
field, that is numbered consecutively? If not you could add an Autonumber
field and it MIGHT number the fields. However you can't depend on this as
Autonumber fields are just guarenteed to be unique IF it's the primary key
field. Consecutive numbers are not guarenteed.

If youhave something like this, you could put criteria in individual queries
such as:

1001
Between 1001 And 2000
Between 2001 and 3000

If you don't have such a field, you might get some of the records out by
sorting ascending on the primary key field and using the Top 1000. You could
then sort descending and do a Top 1000 and get the last 1000 records. However
Top will also include duplicates in the last spot show you could have more
than 1000 records if not on a primary key field.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Kirk P." wrote:

Query 3: records 2001 - 3000

sorry


"Kirk P." wrote:

I've got a table of 11,000 records. Don't ask why, it's a long story, but I
need 11 queries like this:

Query 1: records 1 - 1000
Query 2: records 1001 - 2000
Query 3: records 2001 - 2000

and so on. Any way to do this?

I used your Autonumber idea and that worked fine for this purpose. Thanks!

"Jerry Whittle" wrote:

Access doesn't have a RowID. Do you have a field, perferrably a primary key
field, that is numbered consecutively? If not you could add an Autonumber
field and it MIGHT number the fields. However you can't depend on this as
Autonumber fields are just guarenteed to be unique IF it's the primary key
field. Consecutive numbers are not guarenteed.

If youhave something like this, you could put criteria in individual queries
such as:

1001
Between 1001 And 2000
Between 2001 and 3000

If you don't have such a field, you might get some of the records out by
sorting ascending on the primary key field and using the Top 1000. You could
then sort descending and do a Top 1000 and get the last 1000 records. However
Top will also include duplicates in the last spot show you could have more
than 1000 records if not on a primary key field.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Kirk P." wrote:

Query 3: records 2001 - 3000

sorry


"Kirk P." wrote:

I've got a table of 11,000 records. Don't ask why, it's a long story, but I
need 11 queries like this:

Query 1: records 1 - 1000
Query 2: records 1001 - 2000
Query 3: records 2001 - 2000

and so on. Any way to do this?

Hi Kirk,

There is another method that does not require a new field. This example
gets groups of 100 records from a major codes table:

For records 1-100:

SELECT MAJOR_CODE, MAJOR_DESCRIPTION
FROM MAJOR_CODES
WHERE MAJOR_CODE Between
(select Max(MAJOR_CODE)
from
(select Top 1 MAJOR_CODE
from MAJOR_CODES
order by MAJOR_CODE))
And
(select Max(MAJOR_CODE)
from
(select Top 100 MAJOR_CODE
from MAJOR_CODES
order by [MAJOR_CODE]));

For records 101-200 simply change the "Top n" values in each subselect:

SELECT MAJOR_CODE, MAJOR_DESCRIPTION
FROM MAJOR_CODES
WHERE MAJOR_CODE Between
(select Max(MAJOR_CODE)
from
(select Top 101 MAJOR_CODE
from MAJOR_CODES
order by MAJOR_CODE))
And
(select Max(MAJOR_CODE)
from
(select Top 200 MAJOR_CODE
from MAJOR_CODES
order by [MAJOR_CODE]));

And so on... In this case MAJOR_CODE is the only key field. It gets a
bit more complicated if you have compound keys.

Clifford Bass

Kirk P. wrote:
I've got a table of 11,000 records. Don't ask why, it's a long story, but I
need 11 queries like this:

Query 1: records 1 - 1000
Query 2: records 1001 - 2000
Query 3: records 2001 - 2000

and so on. Any way to do this?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201004/1