One organization can have many relationships

[barbarao]

Hi. I have an entity table that lists entity type, entity name, etc. I have a relationship table that uses the ID from the entity table and the ID from the company table. Problem is an entity in the entity table can have a relationship with another entity in that table which is not in the company table. The reason that happens is because that entity is not a company but a governing structure of sorts. Any thoughts on how to make this work for running views and seeing names? Thanks.

[zmbd]

it's called a join table.
it allows a many to many relationship to be established.
You should take a look at the follwing article.

Defining Table Relationships

This article describes how to define relationships in a Microsoft Access database. It includes the following topics:
•What Are Table Relationships
•Types of Table Relationships
◦One-To-Many Relationships
◦Many-To-Many Relationships
◦One-To-One Relationships


•How to Define Relationships Between Tables
◦How to Define a One-To-Many or One-To-One Relationships
◦How to Define a Many-To-Many Relationships


•Referential Integrity
•Cascading Updates and Deletes
•Join Types

[barbarao]

it's called a join table.
it allows a many to many relationship to be established.
You should take a look at the follwing article.

Maybe I wasn't clear. An entity in the Entity Table may have a relationship with another entity in the Entity Table and may also have a relationship with a company in the company table. Hope that clarifies. Thanks and looking forward to your response.

[Rabbit]

Why not combine the entity and company table? If the difference between the two is merely descriptive, there's no need to separate them.

[barbarao]

Why not combine the entity and company table? If the difference between the two is merely descriptive, there's no need to separate them.

There is a huge difference between the two. We capture very detailed info about the company. For the entity, we only capture name, entity type, main contact. I need to be able to show all relationships under the highest level of entity type and am just stuck on how to do that with the entity to entity issue. Any other thoughts? Thanks in advance.

[Rabbit]

Then you can create a relationship table with the following fields: EntityID, RelatedEntityID , RelatedCompanyI D. And to get information from both tables, you'll just need to left join to both tables.

[barbarao]

Then you can create a relationship table with the following fields: EntityID, RelatedEntityID , RelatedCompanyI D. And to get information from both tables, you'll just need to left join to both tables.

I have a relationship table already. My problem is displaying the data. Let's say I have an entity named California and its ID is 4. I also have an entity named Neveda and its ID is 5. If I relate the two entities in the relationship table, I can see California is related to ID5. I'd like it to be California is related to Neveda. If I relate California to a company in the company table, I see California and CompanyName. Hope that is clear.

[barbarao]

Then you can create a relationship table with the following fields: EntityID, RelatedEntityID , RelatedCompanyI D. And to get information from both tables, you'll just need to left join to both tables.

Just re-read your message and as usual I see something that I didn't on the first read. Let me try that and see if it does the trick. Might not be able to do that till tomorrow; new deadline just came in. Thanks.

[barbarao]

Then you can create a relationship table with the following fields: EntityID, RelatedEntityID , RelatedCompanyI D. And to get information from both tables, you'll just need to left join to both tables.

Tried your suggestion (heck with the new deadline) and when I create a view, I only see one name from the entity table.

[Rabbit]

You're probably only seeing one name because you only brought in one name. If you want both names, you need to bring in both names from the two outer joined tables. But that's only a guess because you didn't post your SQL.

[barbarao]

You're probably only seeing one name because you only brought in one name. If you want both names, you need to bring in both names from the two outer joined tables. But that's only a guess because you didn't post your SQL.

It was how I set up the related entity ID. Thanks as usual for your help. Enjoy the rest of the day.

[Rabbit]

Not a problem, good luck with the rest of your project.