Split a table into 2

  • Thread starter Thread starter David
  • Start date Start date
D

David

There is an existing table containing data as follow

A Borrower1 Book1 DateOfReturn1
B Borrower1 Book2 DateOfReturn2
C Borrower1 Book3 DateOfReturn3
A Borrower2 Book4 DateOfReturn4
B Borrower2 Book5 DateOfReturn5
A Borrower3 Book6 DateOfReturn6
A Borrower4 Book7 DateOfReturn7
B Borrower4 Book8 DateOfReturn8

and we want to split it into two tables

<Table1>
ID1 Borrower1 Address1
ID2 Borrower2 Address2
ID3 Borrower3 Address3
ID4 Borrower4 Address4

<Table2>
BID1 ID1 Book1 DateOfReturn1
BID2 ID1 Book2 DateOfReturn2
BID3 ID1 Book3 DateOfReturn3
BID4 ID2 Book4 DateOfReturn4
BID5 ID2 Book5 DateOfReturn5
BID6 ID3 Book6 DateOfReturn6
BID7 ID4 Book7 DateOfReturn7
BID8 ID4 Book8 DateOfReturn8


Can we split the database to two without index easily with
queries ? And how to create index after spliting ?
 
There is an existing table containing data as follow

A Borrower1 Book1 DateOfReturn1
...

and we want to split it into two tables

<Table1>
ID1 Borrower1 Address1
...
<Table2>
...


Can we split the database to two without index easily with
queries ? And how to create index after spliting ?
Click to expand...

Don't you need THREE tables - a table of Borrowers, a table of Books,
and a table of Checkouts? A given borrower may borrow several books,
or borrow the same book twice!

To answer your question: yes, this can be done readily with queries.
I'd suggest creating the new tables empty, and creating Append queries
based on your existing table. Use the "Unique Values" property of the
query to select each borrower only once, and append the borrower
specific information to the Borrowers table; similarly for Books; once
that's done, you can populate the third table if you decide to create
one.
 
Back
Top