Many-to-Many Self Join

[neo651]

I was discussing this in another thread but it was way off topic so I'm reposting it here.



Short version: I need to know how to setup a Self Join for a table with a many-to-many relationship to itself.

Long Version: I have a table of Objects (i.e. Desktop Computers, Access Points, Offices, Personnel). Many of these Objects have other Objects as properties (Users belong to an Office, Desktop Computers belongs to a User, etc.). So, because of things like Users being able to belong to multiple Offices, but also, of course, multiple Users being able to belong to one Office, it needs to be a many-to-many relationships. There's also an ObjectType table and each Object Type can be assigned and ObjectType as a property as well. I'd like for my final forms to be datasheet forms. Each datasheet represents all the Objects in one ObjectType and each column is an ObjectType associated as a property to the datasheet's ObjectType. Here's my current structure:

tblJunctionObjectTypes
-JunctionID pk
-ObjectTypeID fk (The ObjectType receiving another ObjectType as a property)
-PropertyObjectClassID fk (The ObjectType being assigned as property.)

tblObjectTypes
-ObjectTypeID pk
-ObjectCTypeName

tblObjects
-ObjectID pk
-ObjectTypeID fk

tblPropertyObjects
-PropertyID pk
-ObjectID fk (The Object receiving an Object as a property)
-AttributeObjectID fk (The Object being assigned as a property)


How do I setup the relationships and how do I build a query that gets all Objects of a particular ObjectType and all the Objects of ObjectTypes associated <i>to</i> it.

[aytee111]

Um, you say you "have a table of objects" then you list 4 table definitions? I'm confused.

You can link tables to each other using the Relationships window - drag column names from one table to another.

[neo651]

I did say that "I have a table of objects" (tblObjects), but then a couple lines down I said I have a table of ObjectTypes as well (tblObjectTypes). I then explained that these tables need to have a many-to-many relationship with themselves, which requires there be a junction table for each (tblJunctionObjectTypes, tblPropertyObjects).

I understand how to create relationships, what I need to learn is how to make self joins and specifically how to make a self join when it's a many-to-many relationship.

[aytee111]

Sorry, in way over my head here. I would just make them all separate tables.

[brharrii]

Did you ever find an answer to this question? I've been trying to figure out how to do this but I can't find any deffinitive answers.