2 Replies Latest reply on May 12, 2014 3:11 PM by evdh

    new to relational database

    evdh

      Title

      new to relational database

      Post

           I have established a relationship between two tables.  I can now enter the ID number from my "Membership" table onto my "Potential Homes" table as a foreign key and all contact information for the member pops up accurately.  So far so good. Now in my Browse menu, each field has a drop down menu which shows the Value List I created to make the relationship work.  (if I click on 'address', the drop down shows the complete value list of membership 'IDs + last name' which I am using for my foreign key). 
            1.  Should this be the case?
            
           I mistakenly clicked on someone else's info in the address field.  Now one person's address actually reads as "ID+last name" of another member. My problem is that I can not not correct that back to the proper address info.  Changing info in my parent table does not carry over to my child table.  Using the 'other' option at the end of my 'ID+Last name' value list allows me to write in the proper info but clicking OK does not change the info.  I actually deleted the member record from both tables, reentered it properly in the parent table and tried to reeneter it in my child table using the new ID number - but the error keeps returning instead of the correct info - even though it is a different ID number. 
           2. How can I correct my relationship so that simply changing info in the parent table will change it in the child table?

        • 1. Re: new to relational database
          philmodjunk
               

                    (if I click on 'address', the drop down shows the complete value list of membership 'IDs + last name' which I am using for my foreign key). 

               
                     1.  Should this be the case?
               That would depend on the options you've specified for your value list. you can choose to hide the values from the first field so that you only see the names and not the IDs. But last names are not likely to be unique. I suggest you come up with at least a full first and last name for the second field of your value list, if not more info to help users select the correct ID from Membership. I'd use a text field with an auto enter calculation that combines first and last name values in a single field and then specify a "unique values" validation on this text field to make sure that I don't get two records with the exact same first and last names.
                
               
                    2. How can I correct my relationship so that simply changing info in the parent table will change it in the child table?
               You haven't described what method you used to show the address info on your layout after selecting an ID value from the drop down. From what you describe, I would guess that you defined fields in your Potential Homes table that use auto-enter field options to copy the address info from the Membership table. If you were to remove those fields from your table and layout, you can use the field tool to add the actual fields from Membership to your layout and as they show the actual data from Membership instead of a copy of that data, changes made in membership will automatically show on this layout as well.
                
               But sometimes there is a good reason for copying such data. If you have a reason for copying the data, you should be able to update a single record simply by re-selecting the ID number. (but if you used an auto-enter calculation to copy the data, you may need to clear the "do not replace existing value" check box.)
          • 2. Re: new to relational database
            evdh

                 Thank you!  Problem solved.