Relationship problem

Icehousman2

Registered User.
Local time
Today, 10:34
Joined
May 12, 2004
Messages
45
I have three tables. tblAgent, tblRep, and tblOffice. I have included a picture to show the current relationships.

Normally, an office will be associated with only one Rep. When we start dealing with a new office, a single rep is associated with that office. However, occasionally reps will be given exceptions to an office. Meaning that a single Agent within an office will be associated with a different Rep then the Office as a whole. There are a number of reports that I need to create from these tables.

One is a list of each rep and all offices associated with this rep. However, this list should not include offices where you are only associated with an office because you have an exception there.

Two is a list of all agent exceptions for an individual rep and the offices associated with each exception.

Three is a list of all current exceptions that are associated with offices for a particular Rep. For example, if I am a rep I want to know all of the other reps who have exceptions at “my” offices. And the specifics of each exception.

Does this seem easily done with the relationships that I have set up currently? I would obviously like to keep this as neat as possible. Thank you for everyone’s help in advance.
 

Attachments

I'm not sure I understand your business process. However, i think you have a many to many relationship between reps and offices. One of the relationships will be the 'normal' one and others will be exceptions. This suggests that you need a junction table between reps and offices. The junction table will hold the RepID and OfficeID and a field to indicate if it is normal or an exception (and maybe other data).

Does this make any sense?
 
Doesn't the tblAgent act as this junction table. If I add another one, wouldn't I then be relating tblRep and tblOffice in two ways. And wouldn't that cause problems. I'm sort of thinking that I need to leave it the way it is. This might take some messy queries to get what I want. But I think I can make it work. Thanks for the help. And please let me know if i'm way off.
 
What is not clear here is the representation of your business model through your tables.

Obviously, tblOffice represents an Office. I don't think we have a problem there.

Now, what do tblAgent and tblRep represent? Which one of them represents a real person?

If the answer is BOTH then your problem is normalization.
 
Yes they are both real People. tblRep represents our employees, while table agent represents their customers. And both need to be related to tblOffice. Individual offices are also customers of our reps and Agents work in these offices. I would simply take out the agent to rep relationship, but there is the problem with Exceptions.

Let me try and make this more clear. We deal with hundreds of offices, and thousands of Agents. The agents in these offices have a high amount of turnover so it is impossible to accurately track all of them. So for commission purposes we assign offices with reps. The company will get orders from the actual agents but in general these are assigned to reps based on which office they are working with. However, if a rep has a long history with an agent and that agent switches offices, then we grant them an exception. Here is an example:
AgentA recently relocated to OfficeA, but has an existing relationship with RepB when he was previously at OfficeB. RepA currently receives commission on OfficeA, but because of the prior relationship, RepB will be given commission on all orders coming from AgentA even though he now works at OfficeA. I hope that makes sense.

For simplification it might be easier to simply track the exceptions, and not all the agents. However, we need to track as many as possible for customer service reasons.

I hope this helps clarify the situation. Thank you so much for you interest in helping me out with this. What I have seems to work, but I’m afraid I’m going to run into some trouble down the road.
 
OK, let me see if I get this right...

An Office is where a Rep works. Reps don't move around or cross-post.

An Agent is normally associated with an Office but can sometimes be associated with more than one office.

Agents and Reps are people. Offices are places.

If this is the correct model, then here is one possibility.

tblOffice
OFCID, autonumber or other unique identifier, prime key
OFCAdr1, first line of address
OFCAdr2, second line of address
OFCCity, OFCState, OFCPostal etc etc etc

tblPerson
PERSID, autonumber or other unique identifier, prime key
personal data incl. phone, address, etc.

tblRepAgents
PERSID, foreign key to person
OFCID, foreign key to office
IsAgent: Yes/No for Agent or Rep, Yes = AGENT
(Prime Key of this table is the combo of first three fields)
Any other info needed for this type of data, including date this relationship was established. If the person has a special ID number to use as an Agent, put that number here in an Agent record. If the person has a special ID number to use as a Rep, put that number here in a Rep record.

Now, a report of all agents selects for IsAgent=Yes. A report of all reps selects for IsAgent=No. You can sort by office or by person. To do the linkages right, you probably need to write a query to JOIN tblRepAgents to one of the other tables, then write a second-layer query to implement the other JOIN that links to the other table. The result can be used for reports with any "break on" scheme you want.

The tblRepAgents table is the link between a person and an Agent OR a Rep. This structure allows an Agent to be linked to multiple offices. It happens that it also allows an Agent to also be a Rep (but your business rules might disallow this. I'm just telling you what the STRUCTURE allows.) The structure also allows you to have separate "special" numbers such that a double-dipping agent could use different numbers for different offices - or you could choose to assign the same code number each time.
 
First of all thank you for being so patient. Let me try again to be clearer. A rep does not work at an office in the office table. These offices will be customers of the reps. Agents are also customers of reps and they do work at the offices in the office table. An agent is always associated with only one office and customers of only one rep. So a Rep has a list of customers that are offices, and the agents at these offices are therefore also customers of that particular Rep. I’ve attached a picture of the relationships, which I think make this clearer. Now if this was always the case, I think I’d be good to go.

But. Sometimes an Agent that works at an office will not be a customer of the Rep that is associated with that office. Therefore in the Agent table I have created two fields. Exception (yes/no) and the ExceptionID (which will be the RepID of the Rep actually associated with this Agent.) The key is that I need to be able to know not only which rep the Agent is actually assigned to, but also which rep he would have been assigned to if they were not considered an exception. (I’m sure that makes no sense. I’m racking my brain to try and make it clearer). I’m confident that I can achieve the results that I want from this setup, but it might take some messy queries. I’m new to this so I was wondering if there was a better way. Such as what you suggested, creating a tblRepAgent, and some type of unary relationship I presume? When designing these databases, is it better to run with what “works” or keep trying to find a better way?

Thank you so much for your time and help. This forum is awesome.
 

Attachments

That's a lot clearer. Your new realtionship table seems to work but I think you need a link between tblRep.ExceptionID and tblAgent so you know the agent associated with the rep. The link via tblOffice will identify the 'normal' agent.
 
Thanks for the reply. I have one more question however. I would love to relate tblRep.ExceptionID with tblAgent but won't this "confuse" access? I was under the impression that having two seperate relationships between tables will not work?
 
It might, but that's the reality of the situation. Sometimes you can get around this by only applying the relationship at the time of running the query, rather than declaring the relationship in the relationship diagram.
 
Not really a problem to have two relationships between two tables. If YOU are confused, well, .... welcome to the club. :D

With a two-relation case (or a table-relates-to-self case), you simply add the table to the relation pane twice! The second time you add that table, its name becomes Tablename(2). Now working from the table that has the double-barreled linkage, relate one field to the original table and the other to the second copy of that same tablename. Don't worry, in a relationship, a table can appear many times, one for each relationship if you wish. You don't actually copy the table. You just copy the reference to it. When one table relates to many different tables once each, it is no sweat to draw the links. But the case that relates two tables twice in two different ways - that one is always tricky.
 
Thank you both. This has helped me so much. It is so much clearer now. I really do appreciate it.
 

Users who are viewing this thread

Back
Top Bottom