Exporting rows to excel sheet maximum

[Jerome]

Hi all,
We need to take a random sample from a table of around 300,000 rows, and
are having some difficulty. What is the easiest way of doing this?

For smaller tables in the past we have exported the data to excel and gave
each row a random number, sorted by that and got the sample that way. The
65536 row limit of excel (and the windows clipboard) is a major hindrance in
this instance!

We are using Excel and Access 2002.

Thanks

Jérome

 

[Chris Large]

The following SQL will generate a random selection:-

SELECT TOP N tablename.*
FROM tablename
ORDER BY Rnd([PKfield]);

Where N is the number of records you want and PKfield is a
numeric unique field.

hth

Chris

 

[Jerome]

Hi,

We don't have any unique numeric fields in this database. I just tried to
add an AutoNumber field named SORT and it came up with the error message:
"File Sharing Lock Count Exceeded. Increase MaxLocksPerFile registry entry".
I've seen the error numerous times in Google Search but the solution
(increasing the pertinent registry entry) does not work on this occasion.

Also, how do you go about copying 280000 rows of data from Access to Excel
where Excel can only accept 65536 rows on one sheet? I would like to have
the data in 5 worksheets if possible, for further manipulation.


Thanks.

Jerome


The following SQL will generate a random selection:-

SELECT TOP N tablename.*
FROM tablename
ORDER BY Rnd([PKfield]);

Where N is the number of records you want and PKfield is a
numeric unique field.

hth

Chris