Ms-access – Joining two tables with different fields through a link-table

This directly answers your question about "collating" records and "merging" fields. It is not exclusive to joanolo's answer, rather you could combine both techniques to provide a richer set of data. You mentioned a "third table", but you really only need queries to combine your existing tables. There are indeed times when you want additional tables if you need to store additional information or create necessary links as joanolo's answer details. Otherwise, extra tables for combining data can just lead to duplicate information which requires double the work to maintain and can develop discrepancies if not maintained properly (in other words, avoid copying data between tables).

A query, on the other hand, always draws form the current data and so is always up-to-date. The following set of example queries

1. combine fields using string concatenation. There are other ways to combine data.
   • There are some fields that maybe should not be combined, that should be left blank for certain records. To leave a field blank for a certain query, add a field like this Null As [Field Name]
2. rename fields using the SQL AS keyword.
3. use SQL Unions to combine query results into one set of data which can be sorted altogether.

Saved query [Timeline Events]:

SELECT
    Events.[ID] As IDMixed,
    "Events" As IDType, 
    Events.[Event Date] AS [Timeline Date],
    [Event Type] & "; " & [Notes] AS [Timeline Details]
FROM Events

Saved query [Timeline People Births]:

SELECT 
    People.[ID] As IDMixed,
    "People" As IDType, 
    People.DOB AS [Timeline Date], 
    "Born: " & [First Name] & " " & [Surname] AS [Timeline Details]
FROM People
WHERE People.DOB Is Not Null

Save query [Timeline People Deaths]:

SELECT
    People.[ID] As IDMixed,
    "People" As IDType, 
    People.DOD AS [Timeline Date], 
    "Died: " & [First Name] & " " & [Surname] AS [Timeline Details]
FROM People
WHERE People.DOD Is Not Null

Altogether now:

SELECT  [IDMixed], [IDType], [Timeline Date], [Timeline Details]
FROM [Timeline Events]
UNION
SELECT [IDMixed], [IDType], [Timeline Date], [Timeline Details]
FROM [Timeline People Births]
UNION
SELECT [IDMixed], [IDType], [Timeline Date], [Timeline Details]
FROM [Timeline People Deaths]
ORDER BY [Timeline Date]

The reference to the source table requires not only the ID, but also something that indicates the source table. To present a related record, for example to show the Event details when the user selects a timeline event, the usual technique (based on proper, normalized table design) is to create a bound subform which can automatically show related records from another table or query. But this technique also works only when a foreign key (e.g. ID values) refers to a single table. There is no built-in behavior to switch between multiple related tables as in this case with the combined data. Eventually it will require more advanced Access programming techniques. Here is an outline of how I would proceed, but if you have difficultly, you may need to explore possibilities beyond this question and answer, perhaps posting new questions on Stack Overflow since this goes beyond Database Admin and design.

1. Create a main form with the combined timeline query as the record source.
   • Along with other desired fields, add controls for [IDMixed] and [IDType] fields but probably make them hidden (i.e. Visible = False).
2. Create separate subforms for each table type: People and Events
   • A subform is really just a normal form, only that it is designed to be displayed in a subform control of another form. You create and design it like any other form. In other words, there is no special "subform" type.
   • Make sure that a control for the [ID] fields are on each form, even if you make them hidden.
3. Add both subforms to the main form.
   • Make them both hidden: Visible = False.
   • Position them so that they are on top of each other.
   • In the subform Property Sheet, set the Link Master Fields to IDMixed and the Link Child Fields to ID.

4. In the main form's OnCurrent event, add the following VBA code. Note that this is only a template. At the least, exact names of controls will need to be updated.

5. There are other more advanced techniques, perhaps more elegant or efficient, but they require more advanced programming techniques and understanding of Access intricacies.

Private Sub Form_OnCurrent()
    On Error Resume Next
    If Me!IDType = "Events" Then
        Me.EventsSubForm.Visible = True
        Me.PeopleSubForm.Visible = False
    ElseIf Me!IDType = "People" Then
        Me.EventsSubForm.Visible = False
        Me.PeopleSubForm.Visible = True
    End If
    If Err.Number <> 0 Then
        Me.EventsSubForm.Visible = False
        Me.PeopleSubForm.Visible = False
    End If
End Sub    

Besides the general structure which these queries outline, the specific fields which you include is completely of your design and choice. I assume you will want to include more types of timeline dates, but all you do is add another query and combine it by appending another UNION.

Notes about Union: It is not required to save the other queries separately--the SQL from the original queries could be included directly with the UNION statements, but saving them separately makes it easier to view, edit and verify each separate query individually. It is technically not required that the columns of each query have the same names, only that they have the same number of columns and are of compatible data types. The resultant query will take columns names from the first query in the union.