Hi Keith. Way too vague. Sorry. Can you elaborate with maybe some examples? Customers/Orders/Products? What are we dealing with here.

Hi Richard, Thank you for your response.  Sorry I tried to keep it simple in the hope that it would be easier to explain.  Groups of care homes/care homes/owners of care homes. Because the database covers a long time period and the care homes could be sold the care homes could be in a number of different groups or owners, the group could contain different care homes or owners, the owner could own different care homes and groups.  I am not sure that this will help?

One Many-to-many-table with all 3 foreign keys?

So your tables are:

GroupT
CareHomeT
OwnerT

If homes can be in different groups AND/OR you care about tracking sale/history, then you'll need a junction table for that. Same for the owner. So you'll need:

CareHomeXGroupT
OwnerXCareHomeT

If you're still stuck, let me know and I'll add it to my list for a TechHelp video. This is a concept that confuses a lot of people.

Can the owners own only a care group, or can they own an individual care home? It makes a big difference, in that you'll want to go with the lowest common denominator, which would be care home. Anything else will get quite confusing in queries. I'm guessing you'll have dates of ownership. I'm not sure of the purpose of a care group unless you're trying to categorize the types of care homes apart from who owned them.

It sounds like you might also need GroupXOwnerT to create the final many to many relationship.

Thank you for the comments.  Lars, I tried the one many to many with three foreign keys but ran into problems when updating on the sub forms. Thomas, they can own multiple care homes and be part of multiple groups. Matt, that was what I thought I need to do, alas I don’t know how. Richard, I am looking for a map or diagram on the relationships as the way I tried does not seem to work and I would be most grateful to see the answer in one of your excellent videos.

Hi Keith
I think Richard gave you the solution. If you need help with many to many let us know.
John

Many-to-Many

John and Alex, thank you for your replies. Please could someone let me see a map or diagram of the actual relationships and joins? Many thanks.
Keith

Relationships

CareHomeXGroupT
CareHomeID | GroupID
OwnerXCareHomeT
OwnerID | CareHomeID
GroupXOwnerT
GroupID | OwnerID

Thank you Alex, would these be.3 separate junction tables or would they be interlinked in some way?
Keith

Richard starts discussing relationships in Access Expert 1.
Many-To-Many relationships are discussed in Access Expert 7.

Yes, it would be important to know if owners own just individual homes, or if they can own whole groups. In the latter case you'd need that extra junction table. What do you do if a group has no owner(s)?

Hi Richard, the precedence would be that a person owns a care home and the care home is part of a group of care homes. A group can have many care homes and many owners, a care home can be in many groups and have many owners and an owner can have many care homes and be in many groups.
Keith.

It depends. You could have one table and JoinType column but then you'd need to make sure you're only allowing the ID of the type you want, making sure which is the LHS and which is the RHS then still filter by the type and JOIN in the subsequent Queries. So separate might be simpler to manage.

Sorry Richard, I forgot to say that if a group has no entries it is blank. If there is a care home then there is always an owner.
Keith

Yes Alex, simpler sounds good to me. Is it possible for you to show the joins and types visually in a map or diagram?
Keith

I'm not at a computer and doing it on the phone won't look so good.
I shared the structure above too.
But if you see the Many-to-Many image I shared above that's all you need. One of your tables on the left, the other one on the right and that's your junction.

Did you watch the TechHelps?

Hi Alex, thank you for your time. Yes I did watch the Tech Helps but the ones that I found only show linking 2 tables, not 3. I was really only looking for a point in the right direction like perhaps a screenshot of a relationship screen which showed 3 linked/joined tables.
Many thanks to everyone who has commented.
Keith

So it's Owner > Home > Group. Owners don't own groups directly. That simplifies things.

OwnerT <> OwnerXHomeT <> HomeT <> HomeXGroupT <> GroupT

With that setup, a home can have multiple owners and belong to multiple groups.

I'll add this to the list for a TechHelp video.

Thank you Richard.
Keith