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


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—

  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.