• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 301
  • Last Modified:

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.
SplitTable.mdb
0
bobby6055
Asked:
bobby6055
  • 6
  • 6
  • 3
1 Solution
 
HainKurtSr. System AnalystCommented:
does this work?

insert into table1 from mytable where ...
insert into table2 from mytable where ...
insert into table3 from mytable where ...
0
 
bobby6055Author Commented:
HainKurt:
I am a novice and really not an sql guy. I want a real solution to the question.
0
 
hnasrCommented:
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;
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
hnasrCommented:
"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.
0
 
bobby6055Author Commented:
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.
0
 
hnasrCommented:
ok here is the database!
Run Form1
SplitTable.mdb
0
 
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
0
 
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
0
 
bobby6055Author Commented:
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;

Open in new window

0
 
HainKurtSr. System AnalystCommented:
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);"

0
 
hnasrCommented:
" 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.
0
 
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.
0
 
hnasrCommented:
Try this model. Rearranged tblMain to reflect the proper Normalization process.

 
SplitTable.mdb
0
 
bobby6055Author Commented:
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
0
 
hnasrCommented:
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now