2 Replies Latest reply on Dec 5, 2013 6:51 AM by Phil_1

    Dropdown list populated by two different tables



      Dropdown list populated by two different tables


           This is two problems with a similar issue.  I'm working on creating a list of notes receivable and the owners of companies.  One table contains individuals with fields similar to a contact list.  The other table contains companies and the necessary fields describing those.  I need to be able to select ownership for each company which can be either a company or an individual.  And, select notes receivable to each of the companies which also can be from either an individual or another company.  I would assume I would need one additional table to contain the notes.  However, I figure the company table would be the best place to store the ownership data.  The question is how to join the tables in order to select the individuals and/or companies during data entry for each of the notes and how to select individuals and/or companies to populate the ownership fields in the company table.

           Thanks in advance.

        • 1. Re: Dropdown list populated by two different tables

               The best approach is to set up a unified table of owners. This could be a separate table with links to both the individuals table and the Companies table to record details specific to each. Another option is to combine the data from individuals and companies into a single table leaving empty those fields only needed for the other record type.

               There is no hard and fast rule that says one approach is better than the other, but I am more likely to use separate tables if there is a lot of difference in the data stored for the two types and to use a single table if just a few extra fields are needed for one type and not the other.

               Either way, the unified table solves your value list issue and can also simplify some reporting and other database design tasks.

          • 2. Re: Dropdown list populated by two different tables

                 Thank you.