Split a single table into three smaller ones.

I will like to explore the possibility of splitting a single table (tblMain) into three smaller ones 9tblM1, tblM2 and tblM3 then rejoin them programmatically such that the data from the 3 joined tables has the same data contained in the originally table (tblMain).
Note: This question is part one in a series of other questions that may follow if this question is completed.

I have attached a sample table for the experrimentation and I am hoping to all ideas.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HainKurtSr. System AnalystCommented:
does this work?

insert into table1 from mytable where ...
insert into table2 from mytable where ...
insert into table3 from mytable where ...
bobby6055Author Commented:
I am a novice and really not an sql guy. I want a real solution to the question.
Hamed NasrRetired IT ProfessionalCommented:
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
Select * from tblM2
Select * from tblM3;
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Hamed NasrRetired IT ProfessionalCommented:
"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.
bobby6055Author Commented:
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.
Hamed NasrRetired IT ProfessionalCommented:
ok here is the database!
Run Form1
bobby6055Author Commented:
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
HainKurtSr. System AnalystCommented:
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
bobby6055Author Commented:
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
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;

Open in new window

HainKurtSr. System AnalystCommented:
you need to issue ddl commands to add indexes...



Hamed NasrRetired IT ProfessionalCommented:
" 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.
bobby6055Author Commented:
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.
Hamed NasrRetired IT ProfessionalCommented:
Try this model. Rearranged tblMain to reflect the proper Normalization process.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bobby6055Author Commented:
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
Hamed NasrRetired IT ProfessionalCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.