Creating a table control in base with columns from two tables

[Robin_Dawes]

I have two tables. Table A contains three columns, Author, Title, and Source. Table B contains two columns: Abbreviation and Full_Name. The Source column is linked to the Abbreviation column in a many-one relation.

I have a form (based on some very useful examples posted here!) which lets me choose an Author and then (via a table control in a subform) show all of the Titles by that Author, together with the Source values.

Instead of displaying the Source values, which are abbreviations, I would like the full names to show in the table control. Is this possible?

Example

Author | Title | Source
Smith | Article 1 | NYT
Jones | Article 2 | TS
Smith | Article 3 | GM

Abbreviation | Full_Name
GM | Globe and Mail
NYT | New York Times
TS | Toronto Star

When I search on Smith, the result is a table control showing

Article 1 | NYT
Article 3 | GM

I would like to get a table control showing

Article 1 | New York Times
Article 3 | Globe and Mail

I am very new to working with subforms. Any help would be very welcome!

[RobertG]

You couldn’t get this into one tablecontrol by subforms. A tablecontrol is only part of one form. So the source for your form should be a query or you show the “Full_Name” by a select control.
Query:

SELECT "Title", "Full_Name" FROM "Table1", "Table2" 
WHERE "Table1"."Source" = "Table2"."Abbreviation"

This is a query where you couldn’t add a new entry.

If you set “Table1” as source of the form you could change “Source” to a select control.
Code for this select control is

SELECT "Full_Name", "Abbreviation" FROM "Table2"

This will show the “Full_Name” and will save the “Abbreviation”.

[Robin_Dawes]

Thank you! This achieved exactly what I wanted.