0

Im a complete novice to Access (2013).

Im doing some History research and thought it would be easier to use Access. I have created two table so far.

  1. People, Details info about them
  2. Events (historical).

Both have different fields.

Can I create a third table that collates all the info from the first two tables?

Therefore, I can 'organise' the dates to display in order. (may need to look at DOB and Event Date to merge under name field name to do this?)

I've uploaded the file for you to see if it helps. I tried to search for answers before posting but nothing along the lines im looking at was thrown up. Also have no coding experience, so this is completely new to me.

Thank you in advance

Link to Database File

  • What you want to do is actually JOIN your tables (MERGE is some else), through a link table. I've changed the title of your question. If you think this title doesn't reflect what you actually intend, please, edit it back. – joanolo Jul 29 '17 at 16:05
  • What is your ultimate goal of joining the tables? What do you want to achieve? Are you aiming for a kind of timeline display? Please provide as much information as possible. – John aka hot2use Jul 31 '17 at 6:32
  • The mention of third tables was just a guess at what needed to be done and the word "join" was injected by joanolo--neither was the primary question. The OP even says that he's a novice and so one must read beyond the exact wording. It was apparent from reading the entire question that the OP wanted to collate different records in date order (i.e. combine --even merge-- tables into a single, sorted list of data). That's what I explained how to do. Why all the down votes on my answer? – C Perkins Aug 1 '17 at 17:29
  • @Tri.Stand I suggest re-editing your question title to be something like "Collate records from two tables into a single, sorted set of records". Collate is the perfect word since it implies combining and sorting in proper order. Adding "sorted" is a bit redundant, but emphasizes the intent. I don't have enough reputation to do this. – C Perkins Aug 1 '17 at 17:35
-2

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.

  • Thank You, but as Ive never used VBA or SQL and completely new to Access, I'm a little (alot) confused. I managed to figure out how to insert code in SQL (Just). When I run the query it displays an syntax error in FROM clause? – Tri.Stand Jul 30 '17 at 5:38
  • Each of the SQL blocks are a separate query (4 of them). In Access 1) Go to the Create ribbon (a.k.a. toolbar tab). 2) Click Query Design. 3) Close the "Show Table" window that pops up. 4) On the Design ribbon choose SQL View (far left View button). 5) Paste SQL code. 6) If you prefer, now you can switch back to Design View. 6) Save the query using the names I suggest. ~ It is traditional to post the SQL code because it more efficient on Stack Overflow (SO) than pasting many screenshots and it can be copied directly. Which query has a syntax error? Hmm... I used your DB to create these. – C Perkins Jul 30 '17 at 6:01
  • I mentioned code in the answer to be complete, because if you continue with Access, learning to write your own VBA functions can be very useful, but VBA code is NOT required for the example queries. – C Perkins Jul 30 '17 at 6:19
  • So played around this morning and seen where I went wrong. I managed to do the above and its bascially what im looking for. Is there a way to add a link to the end of each record (in Timeline Query) so that I could click and show me the record in the original table? giving me all the details or even a pretty form display? Many Thanks – Tri.Stand Jul 31 '17 at 5:59
  • @Tri.Stand I've updated the answer with details linking back to original data. There is more to learn to put this all together. Please upvote / accept if you find this useful. – C Perkins Jul 31 '17 at 16:15
1

People and Events are related thorugh a many-to-many relationship, because an event can have many people related to it, and one person can be related to several events.

This is represented in a SQL database by means of a link-table, which contains (at least) two columns (fields), one indicating the unique id representing the Event, the other one indicating the unique id representing the Person (People). Let's call those columns EventId and PeopleId. The table might contain more columns if they should provide some relevant extra information (let's say you want to know which role the person had with regard to this event, such as "was the organiser", "was an attendant", "was a sponsor", "was the inducer", "led the event", "was one of the lead roles", "just was a passer-by", "was the victim", ...).

The primary key of this table is actually a compound one, comprising both (EventID, and PeopleID). So your table should be defined in a fashion similar to the one depicted in the following image:

Table People_x_Events

You can let Access know how this table is related to the other ones by means of the Relationships Diagram:

enter image description here

The corresponding SQL definition is:

CREATE TABLE [People_x_Events] 
(
    [PeopleID] LONG NOT NULL REFERENCES [People] ([ID]),
    [EventID]  LONG NOT NULL REFERENCES [Events] ([ID]),
    PRIMARY KEY ([PeopleID], [EventID])
) ;
  • Ok, I think I understand that. Had a quick play and didn't seem to get anywhere. I will sit down in a while and have a play around. Thank You – Tri.Stand Jul 29 '17 at 17:26
  • Thank You joanolo, I've managed to get to the point displayed in your images but, they dont seem to 'Join' and display data in the People_x_Events table. Am I missing something? – Tri.Stand Jul 30 '17 at 5:32
  • You have to create the lines relating the tables (draggind and dropping) to let Access know which column from the origin table is related to which column of the target table. They won't display data by themselves. You have to decide which data goes there (which events and people are related), and thus, write it. youtube.com/watch?v=iEMS5ilG5jk – joanolo Jul 30 '17 at 6:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.