Prev: Graphs and charts in Access 2007
Next: Updating specific record on continuous form
From: KARL DEWEY on 12 Apr 2010 15:16
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/Forums.aspx/access-forms/201004/1
>
> .
>
From: mls via AccessMonster.com on 12 Apr 2010 15:35
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/Forums.aspx/access-forms/201004/1

From: ghetto_banjo on 12 Apr 2010 15:50
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.
From: John W. Vinson on 12 Apr 2010 16:40
On Mon, 12 Apr 2010 19:35:08 GMT, "mls via AccessMonster.com" <u55943(a)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]
From: mls via AccessMonster.com on 13 Apr 2010 10:13
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/Forums.aspx/access-forms/201004/1

 |  Next  |  Last
Pages: 1 2
Prev: Graphs and charts in Access 2007
Next: Updating specific record on continuous form