How can I retrieve rows from 11 to 20 ( Selete TOP 10 gives first 10)

[mls via AccessMonster.com]

Using 'Select Top 10' gives me first 10 rows of my table. I need to retrieve
the next 10 records.
I am writing to an excel spreadsheet where I need to write top 10 rows
starting from B2 and next 10 records starting from I2. So how can I split
them? Every time I have to split into 10rows each.

Thanks

 

[ghetto_banjo]

Something like this will get the job done. I don't know your table/
field names...

SELECT TOP 10 primaryKey FROM myTable
WHERE primaryKey NOT IN (SELECT TOP 10 primaryKey FROM myTable ORDER
BY....)
ORDER BY ...

 

[KARL DEWEY]

The 'ten' records you get on a TOP 10 are based upon how the records are
sorted.
So if you can sort differently or number your records to use criteria to
select them.

Do you have an autonumber by chance? Post your table and field names with
datatype. Also sample data.

 

[mls via AccessMonster.com]

Can the query be more generic.i.e to select any of the first 10 rows, next 10
rows, irrespective of order. I don't have autonumber or primarykey, I am
reading from .CSV file and process that data to write to excel template. Is
it mandatory to have autonumber field to just retrieve certain number of
records?


select tabl.*, count(*) as cnt into res1 having cnt <=10;
select tabl.*, count(*) as cnt into res2 having cnt >10;

The 'ten' records you get on a TOP 10 are based upon how the records are
sorted.
So if you can sort differently or number your records to use criteria to
select them.

Do you have an autonumber by chance? Post your table and field names with
datatype. Also sample data.

Using 'Select Top 10' gives me first 10 rows of my table. I need to retrieve
the next 10 records.

[quoted text clipped - 3 lines]

Thanks

 

[ghetto_banjo]

well having one of the fields being unique would make things easier.
In relational databases, tables are NOT sorted; you sort them via
query. So when you want to select the Top 10 records, you need to do
so with an "Order By" to get expected/meaningful results.

Now perhaps when Access links to a CSV, that it keeps it "in order"
according to the rows of the spreadsheet, but that seems like a VERY
dangerous thing to assume. Without having a way to sort them, you
can't be certain that the records in the "first 10 rows" are not also
found in the "next 10 rows" according to a SELECT TOP query with no
ORDER BY clause.

 

[John W. Vinson]

Can the query be more generic.i.e to select any of the first 10 rows, next 10
rows, irrespective of order. I don't have autonumber or primarykey, I am
reading from .CSV file and process that data to write to excel template. Is
it mandatory to have autonumber field to just retrieve certain number of
records?

I'd be inclined in this case to bypass Queries altogether and just do this in
VBA. You can use file operations to open the .csv file and step through it
with a Do Loop, writing it out to Excel.

 

[mls via AccessMonster.com]

John, This is not just dumping. I have read .csv file and transpose i.e
crosstab and apply some filters. Is it possible to get\create rowid atleast
in Access?

 

[John W. Vinson]

John, This is not just dumping. I have read .csv file and transpose i.e
crosstab and apply some filters. Is it possible to get\create rowid atleast
in Access?

One way would be to create a local table with an Autonumber ID field; use
File... Get External Data... Link or the TransferText VBA method to link to
the .csv file, and then run an Append query to append the data to the table
with the autonumber. The autonumber will then increment, and you can use its
value with your TOP 10 to extract subsets of the records.