Easy Way to split up a large Table in MS Access

I have a table in a MS Acces开发者_开发百科s 2010 Database and it can easily be split up into multiple tables. However I don't know how to do that and still keep all the data linked together. Does anyone know an easy way to do this?

---

I ended up just writing a bunch of Update and Append queries to create smaller tables and keep all the data synced.

---

You must migrate to other database system, like MSSQL, mySQL. You can't do in MsAccess replication...

---

Not sure what do you mean by split up into multiple tables.

Are the two tables have same structure? you want to divide the table into two pats ... means if original table has fields A,B,C,D ... then you want to split it to Table1: A,B and Table2: C,D.

Anyways, I googled it a bit and the below links might of what you are looking for. Check them.

Split a table into related tables (MDB)

How hard is it to split a table in Access into two smaller tables?

---

Where do you run into trouble with the table analyzer wizard? Maybe you can work around the issue you are running into.

However, if the table analyzer wizard isn't working out, you might also consider the tactics described in http://office.microsoft.com/en-us/access-help/resolve-and-help-prevent-duplicate-data-HA010341696.aspx.

---

Under Microsoft Access 2012, Database Tools, Analyze table.. I use the wizard to split a large table into multiple normalized tables. Hope that helps.

---

Hmmm, can't you just make a copy of the table, then delete opposite items in each table leaving the data the way you want except, make sure that both tables have the same exact auto number field, and use that field to reference the other.

---

It may not be the most proficient way to do it, but I solved a similar issue the following way:

a) Procedure that creates a new table via SQL: CREATE TABLE t002 (ID002 INTEGER PRIMARY KEY, CONSTRAINT SomeName FOREIGN KEY (ID002) REFERENCES t001(ID001)); The two tables are related to each other through the foreign key.

b) Procedure that adds the neccessary fields to the new table (t002). In the following sample code let's use just one field, and let's call it [MyFieldName].

c) Procedure to append all values of field ID001 from Table t001 to field ID002 in Table t002, via SQL: INSERT INTO ID002 (t002) SELECT t001.ID001 FROM t001;

d) Procedure to transfer values from fields in t001 to fields in t001, via SQL: UPDATE t001 INNER JOIN t002 ON t001.ID001 = t002.ID002 SET t002.MyFieldName = t001.MyFieldName;

e) Procedure to remove (drop) the fields in question in Table t001, via SQL: ALTER TABLE t001 DROP COLUMN MyFieldName;

f) Procedure that calls them all one after the other. Fieldnames are fed into the process as parameters in the call to Procedure f.

It is quite a bunch of coding, but it did the job for me.