How to manage multiple linked tables thanks to a single form?

[fred1917]

Hi everyone,

I am a beginner user of LibreOffice Base. I can’t figure out how to solve the following problem neatly. I have a series of questions but also some potential leads on which I would like to know your opinion.

Imagine this situation: you organise events and thanks to your database you can keep track of the participants, the events, and who attended what.

1. To begin, I create a table for all the participants T_participants.
2. I create a separate table for each event, ex: T_event1, etc.
   2.1. Am I wrong to create a separate table for each event? Would it be better to create yet another table, some sort of intermediary table that would link participants and events?
3. Inside T_event1, I create an INTEGER primary key and an ID_participant column.
   3.1. I link ID_participant to T_participants.ID.

How can I create a form that enables me to declare that a participant attended multiple events? Would check boxes, linked to each separate event, do the trick?

Is it possible to create a form that neatly displays a participant and all the events they attended, even though the information is split across multiple tables?

For the moment, I have a very rudimentary answer that dates back from my spreadsheet days: in T_participants, there is an Events column, in which I manually write the names of the events separated by comas (as if I were using tags). This solution is “suboptimal” to say the least. Since this database was originally generated from a spreadsheet, the spelling of the names is somewhat inconsistent, that is why I need to create linked tables to facilitate future research.

Thanks a lot for your time!

[Ratslinger]

Hello,

A lot can be found in the documentation:

Manuals → LibreOffice Base Guide

On-Line → LibreOffice 7.3 Help

For your question I end up with three tables.One for Participants; one for Events; one for Attendance.

Attendance is linked to the Participants and within the Attendance record is a list box based upon the Events table.

In order to avoid duplicate Participants at an Event, the Participant ID combined with the Event was declared as Unique in the Attendance table:

A sample is attached with the tables, a single form and one query showing a list of entries:

FormSubForm.odb (13.7 KB)

[fred1917]

Wow, thanks for the incredibly explicit help! Concerning table links, isn’t Attendance.Event supposed to be linked with T_events.ID?

[Ratslinger]

@fred1917
Relations rejects it, possibly due to the unique setting of Participant ID combined with the Event.
.
May try to look at further later on.

[Ratslinger]

@fred1917

Concerning table links, isn’t Attendance.Event supposed to be linked with T_events.ID?

It was my fault in overlooking the data type in Attendance table. Needed to be set to Integer. Changed this & link now OK:
.
Revised → FormSubForm.odb (13.6 KB)