Results 1 to 3 of 3
  1. #1
    shrimp123 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Posts
    2

    Angry Help! Change Primary Key, data in related table...

    Hello,
    i am new to access 2010 and directly encountered a huge problem!

    Background: i need to change the primary key in a table ("stations", Primary key is "station number"), because future entries will result in double entries for this primary key.
    I have another table ("species&stations") which has records of Species for each "station number".
    So I created a new Primary key for the "stations" table, now called "species id", being a bit better in defining a unique station (as it contains the year and the species number, i.e. "451_2010")
    Now the table "species&stations" references/(is in relationship with) the old primary key, identifying a station number for each record.
    How do I get it to recognise the new primary key from the "stations" table, and keeping the records (over 1000...) with all there info.

    Approach so far: I tried to create a new field in "species&stations", which should look up the new primary key from "stations" and then autofill the field. i did not manage this though



    I attached a screenshot to make the relationships more clear!
    Can anybody help???? Nobody at my institute seems to use Access!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,114
    Use UPDATE sql action to populate the new field in species&stations with the new ID value. Then change all queries that are joining these tables. Then change the Relationship setting.

    BTW, advise against using special characters, punctuation (underscore is exception), spaces in any names. Also, don't use reserved words as names. If you do, must remember to enclose in [].
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    shrimp123 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Posts
    2
    Quote Originally Posted by June7 View Post
    Use UPDATE sql action to populate the new field in species&stations with the new ID value. Then change all queries that are joining these tables. Then change the Relationship setting.

    BTW, advise against using special characters, punctuation (underscore is exception), spaces in any names. Also, don't use reserved words as names. If you do, must remember to enclose in [].
    Thanks! I think i am nearly there! I needed to google the code for it though, but i found it! I also got to know the advantage of not having spaces and other special characters...!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 11-13-2011, 06:57 PM
  2. need to use vba to change a property to table data
    By klingoncowboy4 in forum Programming
    Replies: 2
    Last Post: 08-02-2011, 06:35 PM
  3. change data type in make table query
    By jgelpi16 in forum Queries
    Replies: 2
    Last Post: 01-26-2011, 09:37 AM
  4. Combo Box to change primary key #
    By Sharkun in forum Forms
    Replies: 2
    Last Post: 01-11-2011, 04:05 PM
  5. Primary Key change macro?
    By gracin in forum Access
    Replies: 0
    Last Post: 03-06-2009, 10:59 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums