5 Replies Latest reply on Nov 13, 2016 8:58 AM by philmodjunk

    I need relationship help !

    ultradistance

      I have a database with each record representing one account. Within each account record, there are ten different companies the account may have been sent to (one individual field for each company within the same table), along with the results of that effort. Both of these make up the company data set. Also within that single record there is an identifying ID Code, which represents the type of account it is.

       

      What I want to do now is create a table with a portal where I can select or enter an ID Code, and get each instance of that ID Code with each individual company data set. So if with the individual account record I noted that I sent it to four of the ten companies, I want it to return four responses, one for each of the companies I sent it to, with the unique responses (also in a field in the main account database) with the details of the individual record.

       

      I feel like the answer is right there, but I just can't get it to work. Any suggestions would be much appreciated. Thank you.

        • 1. Re: I need relationship help !
          coherentkris

          ask yourself if a company can have more than one account and an account can have more than one company. If yes to both questions you need a join table.

          • 2. Re: I need relationship help !
            ultradistance

            The answer to both is "yes".

             

            I'm struggling with how to structure that join to bring the single record with multiple fields in it I want to pull out. In that base record I have "Account Name", "ID Code", "Submitted company A", "Result company A", "Submitted company B", "Result company B", etc. etc.

             

            I've tried a range of joins and can't seem to wrap my head around the answer.

             

            Thanks.

            • 3. Re: I need relationship help !
              erolst

              ultradistance wrote:

               

              The answer to both is "yes".

               

              I'm struggling with how to structure that join to bring the single record with multiple fields in it I want to pull out.

              A join table is joined to two (or more) parent tables. That gives you

               

              Account -< JoinTable >- Company (where one -< many)

               

              implemented as

               

              Account::id = JoinTable::id_account, and Account::id_company = Company::id

               

              Now you need to decide which fields to transfer.

               

              ultradistance wrote:

              In that base record I have "Account Name", "ID Code", "Submitted company A", "Result company A", "Submitted company B", "Result company B", etc. etc.

              Ask yourself what attributes you will use to describe the child. That is usually quite easy: every field that has an enumeration (in this case, an alphabetical one, but still) is a candidate. Take the generic form of each field and create it in the join table, like

               

              Join::submitted

              Join::result

               

              then delete it from the Account table. You also need several fields to maintain your structure: a primary key for the join table, plus two foreign keys, one for each parent:

               

              Join::id (non-empty, unique, auto-enter)

              Join::id_account (same data type as the primary key field of Account; must be set when creating a record)

              Join::id_company (ditto for Company)

               

              which of course means that you need these primary keys in the parent table. Not sure if yourID Code already is such a key, or if it is a piece of business data.

               

              What you need to figure out at this point is how to facilitate data entry, i.e. assigning companies to an account. If you need help here, don't hesitate to ask.

               

              btw, I'm in the process (late stages, actually) of developing a Fund Management database for a client. Any similarities to your scenario?

              • 4. Re: I need relationship help !
                ultradistance

                Thank you. I think this is the answer, I just need to do more thinking about the structure.

                 

                Your fund management solution sounds like it might be similar. I'm in the insurance business and built it starting with Lotus Approach in '95, and then have been upgrading and improving in FM for ten years at least. It's actually been a lot of fun, but I only dig into it periodically when I'm trying to improve reporting, etc. This project is trying to summarize prior results with certain ID codes as a guide to how future similar opportunities should be handled. The info is there in a raw form, and I can get by do in a range of searches, but I'm trying to have it automatically appear whenever a new account is entered based on the ID Code.

                 

                Thanks for the help.  I may be back for more! Have a great day.

                • 5. Re: I need relationship help !
                  philmodjunk
                  the account may have been sent to (one individual field for each company within the same table), along with the results of that effort. Both of these make up the company data set.

                  Just to be clear, this part of your original set up is not a good approach. Just consider what happens if you need to send the account to 11 or more companies, let alone that this part defeats your attempt to set up a relationship and a portal to show that data.

                   

                  This is the data that you need to move into a join table in order to gain the needed flexibility.