StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>If you have a single table, add an Autonumber field to it.</p> <p>Then create your other tables, and use the Autonumber value from the original single table as the foreign key to join them back to the original data.</p> <p>If you had tblPerson:</p> <pre><code> tblPerson LastName, FirstName, WorkPhone, HomePhone </code></pre> <p>and you wanted to break it down, add PersonID autonumber and then create a phone table:</p> <pre><code> tblPhone PhoneID, PersonID, PhoneNumber, Type </code></pre> <p>Then you'd append data from tblPerson for the appropriate fields:</p> <pre><code> INSERT INTO tblPhone (PersonID, PhoneNumber, Type) SELECT tblPerson.PersonID, tblPerson.WorkPhone, "Work" FROM tblPerson WHERE tblPerson.WorkPhone Is Not Null; </code></pre> <p>and then you'd run another query for the home phone:</p> <pre><code> INSERT INTO tblPhone (PersonID, PhoneNumber, Type) SELECT tblPerson.PersonID, tblPerson.HomePhone, "Home" FROM tblPerson WHERE tblPerson.HomePhone Is Not Null; </code></pre> <p>Someone suggested a UNION query, which you'd have to save as you can't have a UNION query as a subselect in Jet SQL. The saved query would look something like this:</p> <pre><code> SELECT tblPerson.PersonID, tblPerson.WorkPhone, "Work" As Type FROM tblPerson WHERE tblPerson.WorkPhone Is Not Null UNION ALL SELECT tblPerson.PersonID, tblPerson.HomePhone, "Home" As Type FROM tblPerson WHERE tblPerson.HomePhone Is Not Null; </code></pre> <p>If you saved that as qryPhones, you'd then append qryPhones with this SQL:</p> <pre><code> INSERT INTO tblPhone (PersonID, PhoneNumber, Type) SELECT qryPhones.PersonID, qryPhones.WorkPhone, qryPhones.Type FROM qryPhones; </code></pre> <p>Obviously, this is just the simplest example. You'd do the same for all the fields. The key is that you have to create a PK value for your source table that will tie all the derived records back to the original table.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload