AnsweredAssumed Answered

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

Question asked by Craxal on Jun 29, 2012
Latest reply on Jun 30, 2012 by GuyStevens

Title

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

Post

     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.

Outcomes