Many to many relationships schema

[Clive]

Hi

Hope all are well.

I have built several knack databases for a Customer that are running like a breeze and I have been asked to build a simple one that keeps a database of crew working for different Productions. I thought this would be a walk in the park but Knack has beaten me on this one.

My database, as far as I am concerned has 3 x schema

(Production) Each Production Co (parent) can have many crew working as freelancers.
(Crew) Each Crew (child) will work for many Production companies. Many to many connection is added to Crew Schema
(Crew Role) This is where I try to link Production Company with the Crew member and their specific role (from a drop down). Production & crew are connected in Crew Role

Whatever I try to do, I cannot get the connections to work. Does anybody have any simple fix for this and advise where this isn’t working. Maybe the schema is set up wrong.

Appreciate any assistance here.

[JulianKirkness]

The data structure should look something like this:

and the Crew Role object in Knack:

There would be no connection between Crew and Companies (Production)

Does this help?

Julian

[Clive]

@JulianKirkness

Julian

Thanks Julian, that’s great, it works a treat, I knew it was something simple and that is pretty much how it would come together in Access but I figured Knack would build it slightly differently. I can sleep easier tonight