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:
- 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
- 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:
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.