Split a single table into three smaller ones.

does this work?

insert into table1 from mytable where ...
insert into table2 from mytable where ...
insert into table3 from mytable where ...

HainKurt:
I am a novice and really not an sql guy. I want a real solution to the question.

First, be careful from heading to a bad design. Always you can create queries and use them as separate tables.

Back to the question:
DoCmd.RunSQL "Select * into tblM1 from tblMain where fld between '1' And '3'"
DoCmd.RunSQL "Select * into tblM2 from tblMain where fld between '4' And '6'"
DoCmd.RunSQL "Select * into tblM3 from tblMain where fld between '7' And '9'"

To get all records back:

Select * from tblM1
Union
Select * from tblM2
Union
Select * from tblM3;

"fld" is the name of the field in the main table that is used to select respective records in split files. You can use any other relevant field.

hnasr:
I dont quite understand. I pasted your suggestion into an on click event as I assume thi wat you want me to do, I received all kinds of errors.
I think my request is not possible to achieve. Please upload a sample from my original sample.

ok here is the database!
Run Form1
SplitTable.mdb

I am sure that is a misunderstanding here. This is what I wanted, break tblMain into fields as shown below with each table with it own  (pk and fk). Then rejoin all te tables together as a replica of tblMain

tblM1                                            tblM2                                      tblM3
GroupID                                     CourseId                               STypeID
CCon                                           AppDate

try this

select RID, GroupID, CCon into tblM1 from mytable
select RID, CourseId, AppDate into tblM2 from mytable
select RID, STypeID into tblM3 from mytable

-> join :

select a.*, b.CourseId, b.AppDate, c.STypeID
from tblM1 a, tblM2 b, tblM3 c
where a.rid=b.rid and b.rid=c.rid

HainKurt:
I received "syntax Error missing operators in query expression after amending it, below is the correct sql splits tblMain into 3 tables...and the query that joins them
Question:
Is there a way to have (pk and fk)  defined for each table *tblM1, tblM2 and tblM3) such that all the tables are joined cross the (pk / fk) ?

Private Sub Command0_Click()
DoCmd.RunSQL "select  RID, GroupID, CCon into tblM1 from tblMain"
DoCmd.RunSQL "select RID, CourseId, AppDate into tblM2 from tblMain "
DoCmd.RunSQL "select RID, STypeID into tblM3 from tblMain "
End Sub


SELECT a.*, b.CourseId, b.AppDate, c.STypeID
FROM tblM1 AS a, tblM2 AS b, tblM3 AS c
WHERE a.rid=b.rid and b.rid=c.rid;

Select allOpen in new window

you need to issue ddl commands to add indexes...

http://msdn.microsoft.com/en-us/library/bb177891.aspx

"CREATE INDEX pk_tblM1 ON tblM1 (RID) WITH PRIMARY;"
or
"CREATE UNIQUE INDEX ix_tblM1 ON tblM1 (RID);"

" am sure that is a misunderstanding here" Sure it is.

Check this link, to create tableswith primary and foreign keyshttp://msdn.microsoft.com/en-us/library/aa258255(SQL.80).aspx
Split using with HainKurt's idea but appending to tables created.

Thanks for the links but what I really need aper my original  question are as follows:
(1). Create 3 smaller tables from a single that joins together with pk / fk.
       I tried the sql at the links you provided and I got all kinds of errors.
      So if you could assist with item #1, it will be great using these fields.
     
tblM1                                            tblM2                                      tblM3
 pk                                                  fk2                                             fk3
fk2, fk3
GroupID                                     CourseId                               STypeID
CCon                                           AppDate
(2). Compare data in the three smaller tables with tblMain to ensure that
      the joined  3 tables''s data is exactlt the same as tblMain.

hnasr:
Here is my current situation:
I do have some tables in my main db that are too long and I want a technique that will let me break the table up in several pieces, programmatically join the table piese together and then create a new data entry form using the newly created table pieces.
However, n the process I dont want to my data to be compromised.
Note: The sample you offered above:
(1). Did not utilize the (pk/fk)
(2). Data is distorted from my original tblMain
 
Bobby

I gave you the basic idea of normalization. tblM1 has a compund primary key. g & c
tblM2 has fk g
tblM2 has fk c

The data that belongs to g should be unique to g to allow for a table including g to be isolated.
The same applies to a table with c as the foreign key.

If we understand these basic tables, you can extrapolate to mode complicated tables with many fields.

If you think that my example is deviating from the question, then you need a closer look at normalization. Check help for "normalization" .

We don't want real data, only a simple table with few dummy, but correct to show the issue in question.