4 Replies Latest reply on Nov 4, 2010 5:14 PM by moonyounglee

    how can i relate the tables?

    moonyounglee

      Title

      how can i relate the tables?

      Post

      hi, everybody..

      I have a problem...

      there are A table and B table.

      A table have name field, address field.

      B table have three name fields and three address fields in one record.

      I want to use A table as preference.

      thus what i want is when i input name field in B table, address field is filled automatically.

      i success only one name, but can't do it when 2 name field.

      how can i do ?

      here is my screen shot.

      cc.jpg

        • 1. Re: how can i relate the tables?
          philmodjunk

          How would the system know which of the three sets of fields to use?

          A better design approach would be to break up those three addresses into separate records. Since the three records likely need to be grouped together (that's why you put them in one record), you'd add an additional field to store a value that the three address records would have in common.

          If you tell me more about these addresses and how you want to use them. I can explain how to make this work in more detail.

          • 2. Re: how can i relate the tables?
            moonyounglee

            I related B table 1st name and A table name.

            B table 1st name look up the A table name.

            B table 1st address look up the A table address.

            B table 2nd name and address look up the A table name and address.

            I designed B table sets of fields ( name, address ) because every sets must show in one record.

            What i want is following.

            In A table first record, name - John, address - chicage

                            second record, name - charley, address - LA

            when i put "John" into  B table 1st name field, 1st address field filled "chicago" automatically and

             when i put "charley" into B table 2nd name field, 2nd address field filled "LA" automatically.

            is there any way to solve the problem?

            thank PhilModjunk every times...

            • 3. Re: how can i relate the tables?
              philmodjunk

              I designed B table sets of fields ( name, address ) because every sets must show in one record.

              Why? What problem does this solve for you? (It's almost never necessary as there are several ways you can group the three addresses so that they look like one record but are actually separate records.

              To answer you specific question, I mis-read your original question and thought you were looking up addresses from Table B to Table A.

              You'd need three relationships to three different table occurrences of Table A, each matching by a different name field in Table B.

              Table B::Name1 = Table A::Name

              Table B::Name2 = Table A 2::Name

              Table B::Name3 = Table A 3::Name

              To create Table A 2 and Table A 3, click on the Table A box in Manage | Database | Relationships and then click the button with two green plus signs near the lower left corner of the window. This creates a new Table Occurrence that points to the same data source table as Table A.

              If "Table Occurrence" is a new term, see this thread:  Tutorial: What are Table Occurrences?

              • 4. Re: how can i relate the tables?
                moonyounglee

                Thank PhilModjunk very very much.