You will need to setup a 3rd table as a “junction table” to keep track of the relationships, and create a many-to-many relationship between your 3 tables. Here is information on how to set that up:

Thanks for sending the link. It made a lot of sense but think I’m still missing something or may need to make more tables.

I created a junction table which my ‘people’ and ‘business’ tables are both linked to that also has a column for the persons relationship with the business. The primary field is a function that makes:

‘Felicity flower shop-Volunteer’

So when I look at the ‘People’ table there is a column showing the above (the business and how the person is related to it) …it works pretty well.

I’m having trouble though understanding how to have a similar column in the Business Table that shows the person and how they are related to each business and still have it as a proper entity rather than a string so I can enter data properly in either the ‘business’ or ‘people’ table

Not sure if this makes sense its quite hard to describe, I’m starting to think I’ll need to make more tables but hopefully I can get some words of wisdom before I disappear down a rabbit hole of linked tables.

Thanks for the help ScottWorld


Glen

It sounds like you will want to use “lookup fields“, which display information from linked tables:

Thanks again for your help.

Alas I did try Lookup fields by ‘looking up’ a function field in the Junction table to create a string showing the person and their relationship with the business. (the primary field is also a function but this concatenates the business name and the persons relationship with the Business)

The look-up field in the Business table shows the info (person and relationship with the business) but its a string not an ‘entity’ so I obviously can’t connect people to a Business directly in the business table.

Sorry its hard to describe.

Glen