Splitting large table into parts <=60k

M

Max

Newbie to access (using access 2003). I need to split a large table (>65k
rows) into as many parts, each part should not exceed 60k rows. Thanks for
insights.
 
J

John W. Vinson

Newbie to access (using access 2003). I need to split a large table (>65k
rows) into as many parts, each part should not exceed 60k rows. Thanks for
insights.
Click to expand...

Why? Storing the same "kind" of data in multiple tables is essentially never a
good design.

Is there any logical basis, based on the content of the table, on which you
can decide that certain records should go together - a sequence number field,
a date field, anything else?

John W. Vinson [MVP]
 
M

Max

I need to export the sliced parts to xl2003 (where there's a 65k limit per
sheet). The slicing into parts can be simple. Was thinking to just go by the
row#, eg rows1-60k in table1, row 60,001-120k in table2, etc. But I don't
know the steps to do this in access. Thanks.
 
J

John W. Vinson

I need to export the sliced parts to xl2003 (where there's a 65k limit per
sheet). The slicing into parts can be simple. Was thinking to just go by the
row#, eg rows1-60k in table1, row 60,001-120k in table2, etc. But I don't
know the steps to do this in access. Thanks.
Click to expand...

Since Access tables don't have "row numbers" this is more than a bit
difficult. The limit on spreadsheet size in Excel 2003 is, I'm pretty sure,
two billion odd rows; I'm less sure about the exporting (it may be limited to
the older 64K).

Again - do you have a field *IN THE TABLE* which can serve as a row number?
There isn't one builtin, but there very well might be one that was defined
when this table was created. If so, you can use a criterion

BETWEEN 1 AND 60000
on the first export.
BETWEEN 60001 AND 120000
on the second and so on.

Note also that it is not necessary to create a new table to export to Excel;
you can and should export directly from the Query.

John W. Vinson [MVP]
 
M

Max

Thanks, John
Again - do you have a field *IN THE TABLE* which can serve as a row
number?
Click to expand...

Unfortunately, there's none. Is there a way to create a new auto-numbering
col? There's cols to spare in the table.
.. If so, you can use a criterion ..
.. Note also that it is not necessary to create a new table to export to
Excel;
you can and should export directly from the Query.
Click to expand...

Thanks for the criterion, and the note. Could you help list some steps for
me to apply this? As mentioned, I'm a real newbie in access (next to zero
knowledge).

Max
 
D

Douglas J. Steele

The limit on spreadsheet size in Excel 2003 is, I'm pretty sure,
two billion odd rows; I'm less sure about the exporting (it may be limited
to
the older 64K).
Click to expand...

Don't think so, John. It's Excel 2007 that allows 2 billion odd rows.
 
K

Klatuu

From Excel 2003 Specifications:
Worksheet size 65,536 rows by 256 columns

I believe you are thinking 2007 and then, I heard 1 million, but I haven't
used it so I don't know.
 
J

John Spencer

First, add a new column to your table. Make it an autonumber column.

Now you can build a query to get the first 60,000 records using the TOP
predicate.

SELECT TOP 60000 *
FROM YourTable
ORDER BY TheNewField.

The next 60000 is a more complex query

SELECT TOP 60000 *
FROM YourTable
WHERE TheNewField >
(SELECT Max(T.TheNewField)
FROM
(
SELECT TOP 60000 TheNewField
FROM YourTable
ORDER BY TheNewField.
) as T)
ORDER BY TheNewField

The next 60000 after that is gotten by changing the inner query to read
SELECT TOP 120000 ...

There are other ways to do this, for instance use an unmatched query to
identify the next group of records
Build the first query and run it Save the query as q60000


Then use the first query (q60000) in a second query
Save the query below as q120000
SELECT TOP 60000 *
FROM YourTable LEFT JOIN q60000
ON YourTable.TheNewField = q60000.TheNewField
WHERE q.60000.TheNewField is Null
ORDER BY TheNewField

Next query (save as q180000)
SELECT TOP 60000 *
FROM YourTable LEFT JOIN q120000
ON YourTable.TheNewField = q120000.TheNewField
WHERE q.120000.TheNewField is Null
ORDER BY TheNewField

Next Query (Save as q240000)
SELECT TOP 60000 *
FROM YourTable LEFT JOIN q180000
ON YourTable.TheNewField = q180000.TheNewField
WHERE q.180000.TheNewField is Null
ORDER BY TheNewField
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
D

demechanik

First, many thanks for the response, John.
First, add a new column to your table. Make it an autonumber column.
Click to expand...

I got stuck here. Inserted a new leftmost col. Went to design view to
change the data type to Autonumber. Access threw an error msg prompt:
Once you enter data in a table ... . Clearing the msg resets the data
type to text.

How could I proceed to make it an autonumber column ?

Thanks
 
J

John W. Vinson

How could I proceed to make it an autonumber column ?
Click to expand...

I'd suggest copying and pasting the table to a new table, design mode only (no
data). Add the Autonumber field to the *empty* table, and run an Append query
to migrate your data into it.

John W. Vinson [MVP]
 
M

Max

Thanks, John. I'll try to follow through your steps.
(I'm more at home in excel, so lost in access.)
 
Top