If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How can I retrieve rows from 11 to 20 ( Selete TOP 10 gives first 10)
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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201004/1 |
#2
|
|||
|
|||
How can I retrieve rows from 11 to 20 ( Selete TOP 10 gives first10)
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 ... |
#3
|
|||
|
|||
How can I retrieve rows from 11 to 20 ( Selete TOP 10 gives first
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. -- Build a little, test a little. "mls via AccessMonster.com" wrote: 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201004/1 . |
#4
|
|||
|
|||
How can I retrieve rows from 11 to 20 ( Selete TOP 10 gives first
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; KARL DEWEY wrote: 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201004/1 |
#5
|
|||
|
|||
How can I retrieve rows from 11 to 20 ( Selete TOP 10 gives first
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. |
#6
|
|||
|
|||
How can I retrieve rows from 11 to 20 ( Selete TOP 10 gives first
On Mon, 12 Apr 2010 19:35:08 GMT, "mls via AccessMonster.com" u55943@uwe
wrote: 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. -- John W. Vinson [MVP] |
#7
|
|||
|
|||
How can I retrieve rows from 11 to 20 ( Selete TOP 10 gives first
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 wrote: 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201004/1 |
#8
|
|||
|
|||
How can I retrieve rows from 11 to 20 ( Selete TOP 10 gives first
On Tue, 13 Apr 2010 14:13:29 GMT, "mls via AccessMonster.com" u55943@uwe
wrote: 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. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|