Create a single list from two tables

Forum|Forum|7 years ago
May 29, 2018
3 replies
37 views

JoSpencer
New Participant

I am creating a recording database. For each recording (track) I want to be able to select from a list of contributors (vocals, guitar, producer, engineer, etc,etc) to assign to each track.

Individuals are unique
Groups are made up of Individuals
Contributors can be an Individual or a Group

Example:
For “ExampleSong1” the producer is called “Team1”

“Team1” is made up of Individuals John Smith and Bob Jones
Bob Jones is also vocalist
John Smith is also guitarist

the song should be credited
Producer: Team1
Vocals: Bob Jones
Guitar: John Smith

I have two tables: Individuals, Groups

Individuals
contains First, Middle, Last Name.
Column 1 is the formula: {First Name}&" "&{Last Name}
e.g.
John Lennon
Paul McCartney

Groups
links to Individuals table (allows linking to multiple records)
Column 1 is the Group Name
e.g.
The Beatles

I want to be able to select from Individuals and Groups in a single list in another table:
e.g.
Paul McCartney
John Lennon
The Beatles

I have created a table called Contributors, but I can’t worked out how to combine records from Individuals & Groups

Is this possible?

This topic has been closed for replies.

J

+18

Jeremy_Oglesby
Inspiring
Forum|Forum|7 years ago
May 29, 2018

It isn’t possible to reference to different tables (Individuals & Groups) from a single “Linked Records” field.

I’m not totally clear on the structure of your database, so I’ll do my best to word what I see as potential solution sin a way that I think will fit into what you are doing there…

1 solution is:
In your Contributors table - which it looks to me like you are linking to a Track record - you could create another field that checks which of the two has been selected for a particular role, and fills with the one that has been selected. It means 3 fields in place of one in the Contributors table, but ultimately you can do a Lookup on only the 1 “Producer” field from the Tracks table, so the Tracks table will still only show one field for “Producer”:

Another solution is:
Collapse your Groups table into the Individuals table (and perhaps rename the amalgamation to Entities to make it more clear). Create self-referencing links within the table to define groups, and add a “Group Name” field. the “Name” can display “Group Name” if it exists, otherwise “First Name” and “Last Name”; this resolves the problem of multiple “Producer” fields in the “Contributors” table, but makes “Groups” less dynamic as an entity: