Ternary relationship?

[Icehousman2]

Ok, I have a problem. I am creating a customer database, with the tables tblRep, tblAgent, and tblOffice. Each agent is assigned to one rep and one office. However, each office can have more then one rep and each rep may have more then one office. Now after saying that, in general an office will only have one rep. However, in some cases an agent from a particular rep will be related to an office that belongs to another rep, hence the many-to-many relationship. When this happens, this is considered an exception and I need to track this. So, I tried to create a junction table called tblException that included all the primary keys from the three main tables, and an extra field ‘exception’. However, when I try and create a new office I am unable to relate this to the rep table, because I do not have the agentID to create a record in the table tblException. The agent may or may not be added at a later date. So I have a problem. It seems to me that I have this set up wrong. Anybody have some advice? I’ve included a screen shot of the relationships. And if you are wondering officecode is part of the primary key in both the tblagent and tbloffice because of updatability between separate instances of the database.

 

[Icehousman2]

Thanks Pat,

I think you pointed me back in the right direction, but I still have some questions. I think I can now delete officeID from the Agent table, but am not sure if I’m missing something? Also I’m not sure how counting repid in the relation table will allow me to pick out the exceptions because a certain repid could be in the relation table hundreds of times without a single exception. I think I still might be missing something. I’ve included a new picture of the relationships after the changes. Thanks for the help. It is greatly appreciated.

 

[Icehousman2]

Ok, I may be confused or maybe I’m being confusing. Please bear with me. In the first response you mentioned that I need to make officeID and repID unique indexes in the relation table, which I assume means indexing the field and choosing no duplicates. However, there will need to be duplicates of both fields in this table. So, I indexed them but chose duplicates ok.
As for the officeCode field in both the tblAgent and tblOffice tables, I use that field to merge information with other identical databases (structurally). (The office code is different for every office in which the database is held, not necessarily ever workstation.) That static number, in combination with the primary key, gives me a unique number that I can then use to merge info with the other databases.
Also, just so you know what the end result needs to be. I need to be able to create a report for each rep (the users of this database) that will show them all of the exceptions for their offices. So, an office should be “owned” by a Rep. But, another rep may enter an agent under that particular office, only as an exception. So, as I see the database set up now, when I enter an agent as an exception. The exception table contains a record that includes a unique number to relate to the agent table. The rep that is assigned the agent. And the office that the agent is assigned to. It in no way relates the rep that “owns” the office to this record. So, I don’t see how I could create such a report as described above, with this design.
Thank you for all your help. I don’t know what I would do with out this message board.

 

[Icehousman2]

Thanks for all your help Pat. I was obviously more then a little confused, but you really straightened me out. It all seems to be working great. I did keep the relationship between the rep and office tables. Hope that doesn't cause me problems later. I don't think it will. Thanks again.