Break table up into multiple tables by row count?

[Ryan Clair]

Is it possible to break one table up into multiple tables by groupings
of 10?

Example:

Table A has the following information:

Some Text1
Some Text2
Some Text3
Some Text4
Some Text5
Some Text6
Some Text7
Some Text8
Some Text9
Some Text10
Some Text11
Some Text12
Some Text13
Some Text14
Some Text15
Some Text16
Some Text17
Some Text18
Some Text19
Some Text20


I'd like Table A broken up into two different tables:

Table B
Some Text1
Some Text2
Some Text3
Some Text4
Some Text5
Some Text6
Some Text7
Some Text8
Some Text9
Some Text10

Table C
Some Text11
Some Text12
Some Text13
Some Text14
Some Text15
Some Text16
Some Text17
Some Text18
Some Text19
Some Text20


Note:
Each text is unique. I don't care how they are grouped only that they
are broken up into 10's in different tables. The total amount isn't a
multiple of 10 so the last table will have less. Goal is to export
each table into a csv file so if we can automate that process as
well... awesome.

Much thanks,
Ryan

 

[John W. Vinson]

Is it possible to break one table up into multiple tables by groupings
of 10?

It's neither necessary nor a good idea.

If you're assuming that you must have a standalone table in order to do an
export, your assumption is incorrect! You can and should export from a Query
instead. You can use the TOP VALUES function to chip off ten-record chunks:

SELECT TOP 10 *
FROM yourtable
ORDER BY primarykey;

SELECT TOP 10 *
FROM yourtable
WHERE primarykey NOT IN
(SELECT TOP 10 primarykey FROM yourtable ORDER BY primarykey);

SELECT TOP 10 *
FROM yourtable
WHERE primarykey NOT IN
(SELECT TOP 20 primarykey FROM yourtable ORDER BY primarykey);

and so on... replace primarykey with the actual primary key fieldname of your
table. (If your table doesn't HAVE a primary key you may have a problem; do
you have any field which uniquely identifies a record?)

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If this is just a one-time thing you can add an AutoNumber column to the
table. Then create a query like this (in the query's SQL View):

PARAMETERS [Start of Range] Long, [End of Range] Long;
SELECT some_text
FROM table_name
WHERE autonumber_column BETWEEN [Start of Range] And [End of Range]

Then just export this query - you don't have to save the data to tables
first.

To automate this you'd have to use VBA & a loop to run the ranges and
export the query. If you have to do this on a continuous basis you'll
have to have a method of determining which rows have been exported and
which rows are still to be exported. Probably just store the last
ending range number, or delete the rows that have been exported. Then
you'd have to determine the beginning range number for the next export
(you could use the DMin() function).

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSab3SoechKqOuFEgEQKwfgCgmDmY9VSiSWoNBHz+XcyCzOwIUjsAn34E
4U8XNI6RkhvyHMV95q/V4aas
=f8wH
-----END PGP SIGNATURE-----