2 Replies Latest reply on Jun 30, 2012 4:33 AM by GuyStevens

    Combining Data from Multiple Fields to be Displayed in Layouts and Combo-Box Lists



      Combining Data from Multiple Fields to be Displayed in Layouts and Combo-Box Lists


           I am in the process of creating a database for budgeting personal finances.  Part of this database includes a Transactions table where I have records for income and expenditures.  I also have a table of Payees that contain basic contact information, such as addresses, websites, and associated accounts.  Each address element (street addres, city, state, etc.) is stored in a separate field.

           The Transactions and Payees tables are related by a match field called Payee ID, which is just a unique ID number for each payee.  Additionaly, whenever Payee information is updated, the changes are immediately reflected in Transactions.

           Here is the functionality I am trying to achieve but am having a hard time accomplishing:

      1.           The Payee field in the Transactions table is a combo-box.  When opened, it lists all payees from the Payees table.  Although the fields are related by matching Payee ID, the combo-box list displays each entry as text containing a combination of names and addresses, something like this:

      John Doe—123 Some Rd. Nowhere, USA 12345

      Random Bank—Somewhere, USA

      Web Payee—http://www.fakesite.com

      1.           When an entry from the combo-box list is selected, the payee data is displayed in a combo-box not as an ID number, but as a name/address combination, in a standard 3-line address block format in the layout:

      John Doe
      123 Some Rd.
      Nowhere, USA 12345

           I have played with table relationships, scripts, settings, a lot of stuff, but I can't seem to figure out how to do all this, or even if it's all possible.  Any help on this would be greatly appreciated.