Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Location
    Houston, TX
    Posts
    29

    Unhappy Unanswered: Record chages to field in seperate lookup table

    Hello all,

    I have a database that is used to track research proposals. One particular fiels in the tblProtocols is named CPHS. This field represents a unique study identifier which WILL change periodically. My quest is to be able to search for a protocol by either the current CPHS# or a past CPHS#. Thus my thought is to have a seperate table tblCPHSXwalk which will be updated with the Protocols!protocolID value and the new Protocols!CPHS value.

    tblProtocols
    protocolID 'primary key
    cphs
    title
    ...

    tblCPHSXwalk
    protocolID ' both fields set as compund foreign key
    cphs


    If any one have any clue as to how I can achieve this goal I would appreciate the help!



    Thanks,

    MrResearch

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Record chages to field in seperate lookup table

    Originally posted by MrResearch
    Hello all,

    I have a database that is used to track research proposals. One particular fiels in the tblProtocols is named CPHS. This field represents a unique study identifier which WILL change periodically. My quest is to be able to search for a protocol by either the current CPHS# or a past CPHS#. Thus my thought is to have a seperate table tblCPHSXwalk which will be updated with the Protocols!protocolID value and the new Protocols!CPHS value.

    tblProtocols
    protocolID 'primary key
    cphs
    title
    ...

    tblCPHSXwalk
    protocolID ' both fields set as compund foreign key
    cphs


    If any one have any clue as to how I can achieve this goal I would appreciate the help!



    Thanks,

    MrResearch
    Sounds to mean that you did what you're asking about ... table-wise that is ...

    Question: Can your cphs belong to different ProtocolID's?

  3. #3
    Join Date
    Jan 2004
    Location
    Jersey, UK
    Posts
    108
    Sounds like you need to create a new table for storing the protocols and the cphs but in this one don't assign the protocol as a primary key. This would give you a many to one relationship between your original table and your new table, thus enabling you to query current and past cphs's. It might help also to include a timestamp on your new table.

    All you need to do now is create a form for input of the cphs's and make sure that both the original and new tables are updated when you hit that save button.


    Regards - Andy

  4. #4
    Join Date
    Jan 2004
    Location
    Houston, TX
    Posts
    29

    Re: Record chages to field in seperate lookup table

    Originally posted by M Owen
    Sounds to mean that you did what you're asking about ... table-wise that is ...

    Question: Can your cphs belong to different ProtocolID's?
    The relation between the cphs and ProtocolID is 1-1. My lapse in brain function come in how do I update the Crosswalk table when the CPHS number is changed?

    This is probably not too difficult, however I am a bit new to VB and could use a little nudge in the right direction.

    Thanks for the help.

    -MrResearch

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Record chages to field in seperate lookup table

    Originally posted by MrResearch
    The relation between the cphs and ProtocolID is 1-1. My lapse in brain function come in how do I update the Crosswalk table when the CPHS number is changed?

    This is probably not too difficult, however I am a bit new to VB and could use a little nudge in the right direction.

    Thanks for the help.

    -MrResearch
    Simple. Save off the original cphs. In the form that has the cphs edit control have a flag for if it changes ... You can set this flag in the After_Update event ... When you go to update the existing protocol if the cphs has changed write a new "previous cphs" record with the original ...

Posting Permissions

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