Split Table to multiple tables

[Jürgen Germonpré]

Dear all,

I wanted to write a module to split a rather large table into multiple
smaller tables.
i.e. source table has 200.000 records, and I want to split it into tables by
blocks of i.e. 10.000 records.

The splitting is fairly simple: first 10.000 recs -> Table1, next 10.000
recs -> Table2, ....
No particular order...

If coded a module that uses a recordset object and then cycles record by
record, counting them and copying them in a new table until it reaches the
max. number of records, but this is really slow !

So I wondered if there was an other method which is perhaps more efficient.



Thanks.

 

[Douglas J. Steele]

Why? That's a very unusual thing to do. It means that you cannot set up
proper relationships, nor enforce referential integrity in your database.

 

[Robert Morley]

Rather than coding it, could you perhaps run a series of SELECT...INTO
queries followed by DELETE queries? My DAO/Jet syntax is a little rusty,
but I believe it would be something like this:

1) SELECT TOP 10000 * FROM BigTable INTO SmallTable1
2) DELETE TOP 10000 * FROM BigTable
3) SELECT TOP 10000 * FROM BigTable INTO SmallTable2
4) DELETE TOP 10000 * FROM BigTable
.... etc.



Rob

 

[Microsoft]

I'll give you some more information, I'm afraid I wasn't clear enough.

Actualy I don't need relationships at the end of the process! I'm aware of
neat database design (although I'm not a pro I try to do it the way it
should).

In fact its a table full of addresses (+ extra info) that is used as a
database for merging with documents (similar to Word Mail Merge). This table
could be the result of a make table query wich has done all the necessary
work (i.e. adding salutation based on sexcode, adding location based on zip
code and ect...). I'll call this table: tblMERGE (since it's ready to be
merged in a document and will be printed on a high volume laser printer)
This table is sorted in a specific order.
Now to save on printercost we use n-up preprinted paper. In this case 2 up.
So I have address 1 on the left and address 2 on the right.
Now I need to generate a special "indepth" sort because of the fact that
after printing, I cut the stack in half as a whole and still maintaining the
original sort. I can now put the left stack on the right one an my order was
not affected.

For production reasons I need to split up large tables. If I have a large
run (ie. 100.000 recs, takes +- 10h to print), the people doing the
finishing have to wait until all records are printed, then cutting it up and
putting it back in order... Alas, they can't always wait that long....
So what I do, I split up the tables first (ie. 10.000 recs, taking up to 1h
of print !). Then I do the indepth sort and then I spool it to the printer.
So instead of waiting a day for the whole stack to finish, they just have to
wait for the much smaller batches.

Since this happens alot it would be great I had a little module to slice up
the tables in that way.

I hope this is more specific.


JG

 

[John W. Vinson]

In fact its a table full of addresses (+ extra info) that is used as a
database for merging with documents (similar to Word Mail Merge). This table
could be the result of a make table query wich has done all the necessary
work (i.e. adding salutation based on sexcode, adding location based on zip
code and ect...). I'll call this table: tblMERGE (since it's ready to be
merged in a document and will be printed on a high volume laser printer)
This table is sorted in a specific order.

NO, it is NOT.

A table *has no order*. It's an unordered "sack" of records.

And you do *not* need to create a separate table for each batch of export.

You can export, or merge, from a Select query just exactly as easily as from a
table... and that will let you do so with the records sorted, as well.

A Select query will be just one step simpler than a MakeTable query, and
vastly more efficient.

John W. Vinson [MVP]

 

[Microsoft]

Thank you for your info.

Still the question remains: how can i split it up into several batches of
n-records query wise then ?

I tried to find a solution on the web. The thing i like to do resembles the
most to pagination, used to display a determined number of records over
multiple pages on a web page. A lot of solution are based upon stored
procedures...
Is there a way to implement this in access ???

 

[John W. Vinson]

Thank you for your info.

Still the question remains: how can i split it up into several batches of
n-records query wise then ?

I tried to find a solution on the web. The thing i like to do resembles the
most to pagination, used to display a determined number of records over
multiple pages on a web page. A lot of solution are based upon stored
procedures...
Is there a way to implement this in access ???

It's not terribly efficient but you can use a series of "frustrated outer
join" queries (though AFAIK you can't use parameter queries):

SELECT TOP 10000 * FROM tablename
ORDER BY someuniquefield;

SELECT TOP 10000 * FROM tablename AS X
LEFT JOIN (SELECT TOP 10000 FROM tablename AS Y ORDER BY someuniquefield)
WHERE y.someuniquefield IS NULL
ORDER BY someuniquefield;

SELECT TOP 10000 * FROM tablename AS X
LEFT JOIN (SELECT TOP 20000 FROM tablename AS Y ORDER BY someuniquefield)
WHERE y.someuniquefield IS NULL
ORDER BY someuniquefield;

SELECT TOP 10000 * FROM tablename AS X
LEFT JOIN (SELECT TOP 30000 FROM tablename AS Y ORDER BY someuniquefield)
WHERE y.someuniquefield IS NULL
ORDER BY someuniquefield;


John W. Vinson [MVP]

 

[Jürgen Germonpré]

Dear John,

I cannot make this work....
Two things i've changed, i take the TOP 1000, i've added the * to the second
query for the left join.

SELECT TOP 1000 * FROM tblIMPORT AS X
LEFT JOIN (SELECT TOP 1000 * FROM tblIMPORT AS Y ORDER BY Y.ID)
WHERE Y.ID IS NULL
ORDER BY X.ID;

But i recieve: Syntax error in FROM clause.

Then I changed the the join expression adding ON X.ID = Y.ID, and qualified
each ID just in case...
SELECT TOP 1000 X.* FROM tblIMPORT AS X
LEFT JOIN (SELECT TOP 1000 Y.* FROM tblIMPORT AS Y ORDER BY Y.ID) ON X.ID =
Y.ID
WHERE (Y.ID IS NULL)
ORDER BY X.ID;

But now i recieved: Syntax error in the JOIN clause.
???

Maybe I'm overseeing something, ... i've read and re-read but i just don't
see the syntax error now....
Is it because of the nested SQL perhaps ????


JG

SELECT TOP 10000 * FROM tablename AS X
LEFT JOIN (SELECT TOP 10000 FROM tablename AS Y ORDER BY someuniquefield)
WHERE y.someuniquefield IS NULL
ORDER BY someuniquefield;

 

[John Spencer]

You need to assign a name to the sub-query in the the Join clause

SELECT TOP 1000 X.* FROM tblIMPORT AS X
LEFT JOIN
(SELECT TOP 1000 Y.*
FROM tblIMPORT AS Y
ORDER BY Y.ID) AS Y2
ON X.ID = Y2.ID
WHERE Y2.ID IS NULL
ORDER BY X.ID;


Although for efficiency's sake I would probably only return the ID field in
the subquery

SELECT TOP 1000 X.* FROM tblIMPORT AS X
LEFT JOIN
(SELECT TOP 1000 tblIMPORT.ID
FROM tblIMPORT
ORDER BY ID) AS Y
ON X.ID = Y.ID
WHERE Y.ID IS NULL
ORDER BY X.ID;

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Jürgen Germonpré]

Great !
Thanks !

You need to assign a name to the sub-query in the the Join clause

SELECT TOP 1000 X.* FROM tblIMPORT AS X
LEFT JOIN
(SELECT TOP 1000 Y.*
FROM tblIMPORT AS Y
ORDER BY Y.ID) AS Y2
ON X.ID = Y2.ID
WHERE Y2.ID IS NULL
ORDER BY X.ID;


Although for efficiency's sake I would probably only return the ID field
in the subquery

SELECT TOP 1000 X.* FROM tblIMPORT AS X
LEFT JOIN
(SELECT TOP 1000 tblIMPORT.ID
FROM tblIMPORT
ORDER BY ID) AS Y
ON X.ID = Y.ID
WHERE Y.ID IS NULL
ORDER BY X.ID;

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Dear John,

I cannot make this work....
Two things i've changed, i take the TOP 1000, i've added the * to the
second query for the left join.

SELECT TOP 1000 * FROM tblIMPORT AS X
LEFT JOIN (SELECT TOP 1000 * FROM tblIMPORT AS Y ORDER BY Y.ID)
WHERE Y.ID IS NULL
ORDER BY X.ID;

But i recieve: Syntax error in FROM clause.

Then I changed the the join expression adding ON X.ID = Y.ID, and
qualified each ID just in case...
SELECT TOP 1000 X.* FROM tblIMPORT AS X
LEFT JOIN (SELECT TOP 1000 Y.* FROM tblIMPORT AS Y ORDER BY Y.ID) ON X.ID
= Y.ID
WHERE (Y.ID IS NULL)
ORDER BY X.ID;

But now i recieved: Syntax error in the JOIN clause.
???

Maybe I'm overseeing something, ... i've read and re-read but i just
don't see the syntax error now....
Is it because of the nested SQL perhaps ????


JG

SELECT TOP 10000 * FROM tablename AS X
LEFT JOIN (SELECT TOP 10000 FROM tablename AS Y ORDER BY
someuniquefield)
WHERE y.someuniquefield IS NULL
ORDER BY someuniquefield;

On Sun, 29 Jul 2007 13:03:25 +0200, "Microsoft"
<ricosjet@hotmail[DOT]com>
wrote:

Thank you for your info.

Still the question remains: how can i split it up into several batches
of
n-records query wise then ?

I tried to find a solution on the web. The thing i like to do resembles
the
most to pagination, used to display a determined number of records over
multiple pages on a web page. A lot of solution are based upon stored
procedures...
Is there a way to implement this in access ???

It's not terribly efficient but you can use a series of "frustrated
outer
join" queries (though AFAIK you can't use parameter queries):

SELECT TOP 10000 * FROM tablename
ORDER BY someuniquefield;

SELECT TOP 10000 * FROM tablename AS X
LEFT JOIN (SELECT TOP 10000 FROM tablename AS Y ORDER BY
someuniquefield)
WHERE y.someuniquefield IS NULL
ORDER BY someuniquefield;

SELECT TOP 10000 * FROM tablename AS X
LEFT JOIN (SELECT TOP 20000 FROM tablename AS Y ORDER BY
someuniquefield)
WHERE y.someuniquefield IS NULL
ORDER BY someuniquefield;

SELECT TOP 10000 * FROM tablename AS X
LEFT JOIN (SELECT TOP 30000 FROM tablename AS Y ORDER BY
someuniquefield)
WHERE y.someuniquefield IS NULL
ORDER BY someuniquefield;


John W. Vinson [MVP]

 

[Jürgen Germonpré]

Dear Sir,

To continue on your remark, that a table has no order even if you create it
through a make table.

Q1: Suppose I recieve this table created in a certain order which I have to
maintain. It could be a table generated by this maketable query
f.i. SELECT * FROM tblIMPORT INTO tblMERGE WHERE (TOEXCLUDE = True) ORDER BY
aField;

So if I now want to add a unique ID, by adding an Autonumber field ID to the
existing table, it is possible that the records could recieve an unique ID
but possibly not in the intended order.
I suppose it would be better to create an new table structure with an
autonumber ID, and then doing an APPEND of all the records in that
particular order to the new table. (INSERT INTO .....)
Correct ?


Q2: At the moment I'm doing this in vba using a recordset, but it is
extremely slow, I wondered if there was a less time consuming way.
Is there a way to 'autonumber' records the following way with a query.
------------------
Name ID
-------------------
Ben 1
John 3
Kid 5
.... (record supposed to be exactly in the middle or one less of the
table...)
Riddly 2
Kathy 4
Roddy 6
.....

Numbering is 1 step 2 until reaching the middle of the table t.i.: record
number >= total records in the table and then continuing with 2 step 2.
I need to do this in order to create 'stacks' for printing. The step could
be any other number but 0 and 1 depending on the number of stacks I need.
(f.i. 2-UP, 4-UP)
Then I create a query which will sort on ID so the result will be a
controlled 'mixup' of records in this way:
------------------
Name ID
-------------------
Ben 1
Riddly 2
John 3
Kathy 4
Kid 5
Roddy 6
.....


Thanks.

JG

 

[John W. Vinson]

So if I now want to add a unique ID, by adding an Autonumber field ID to the
existing table, it is possible that the records could recieve an unique ID
but possibly not in the intended order.

You can certainly ensure that the ID - whether autonumber or manually assigned
- is in the correct order.

But that does not mean that the Table will always be presented, or exported,
in that order.

The records in the table will be displayed in whatever order Access finds
convenient, unless you either specify an OrderBy property in table view (which
in fact creates a hidden Query in your database), or you create a Query
yourself.

Again... relying on the order of records in a Table *IS A MISTAKE*. It will
not work reliably, it is not necessary, and it's a misuse of tables.

Use tables to store data.
Use queries to sort and select data.

John W. Vinson [MVP]