Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a problem, I have two tables that currently are unrelated, however the first table has the values in a number field which would be correct as primary key numbers, however they start from 427 and goto 980, but with gaps. The other table has the correct numbers in a number field that could be used as foreign key to the first table.

    Problem is how do I do it, can I do it. Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    There is in itself no objection against gaps in the values of the primary key of a table.

    Do you mean that the second table has values in the 'foreign key' field that do not occur in the 'primary key' field in the first table? If so, you'll have to add the missing values to the first table before you can create a relationship with referential integrity enforced. You may be able to do that by using the Find Unmatched Query Wizard to create a query that returns the missing values, then change it into an append query.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Hans. My problem was the table that would have made the primary table had a number field filled with values which I wanted to convert to being an autonumber field. The second table also had a number field with the correct values in could have then been the foreign key. However I resorted to writing code to insert the tables into new related tables and that sorted it out. Had there been a way to convert an existing number field into an autonumber without losing the values would have made it easier. Many thanks, Regards

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    For the future: you can use an append query to add records to a table and populate an AutoNumber field with existing values.

Posting Permissions

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