Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004

    Unanswered: Splitting table into 2

    I have a table that was imported from excel and I want to split it into 2 tables which I have already created. its a set of contacts need to take the address out as the same contact could have 2 addresses. so how can I put the contact information into one table and the address into another keeping them linked.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    ideally use a SELECT INTO query or in Access speak use a make table query

    however it may be easier to fudge it within Access
    first off, belt and braces take a copy of your access db
    then modify your existing table, assign a primary key (probably an autonumber), call it say ID
    then save the table
    copy that table (the copy will have the address data)
    then in the first table delete the address data columns
    then save the table
    open the other table, in design mode remove the primary key flag from ID BUT RETAIN the column. rename that column to be say ContactID this provides the track back to the contact details table (the first table)
    remove the non address columns EXCEPT what was the primary key column
    define a new primary key in the address table, again called say ID
    save the table

    open the relationship designer(database tools | relationships) and define a relationship between the first table and the second table (do this by clicking on the first tables primary key (ID) and dragging to the second table's old proimary key called say contactID

    enforce relational integrity (RI) and decide if yoiu want delete cascade or not, probably not

    then you need to go through your data and tidy it up
    re assign the contactid in address for contacts that appear twice int he contacts table, then delete the duplicates on contacts
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2004
    thanks for you reply I didn't do it that way but it helped

    Thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts