Jump to content

Relationship Issue


doozeracts

This topic is 6066 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Ack! I give up on this one, can anyone help me sort out this relationship?

So I have a database for my company that consists of the following tables:

Account Reps

Companies

Individuals

Status

Account Reps are employees in our office.

Companies are the businesses we sell to. There is ONE Account Rep for every Company.

Individuals are people in those companies

Status is the status of each Individual (lead, potential, active, inactive)

In a layout for Account Reps, each Rep has their own record (determined by field "Rep Name").

I would like to have three different portal views of Individuals: One for [color:red]Leads, one for [color:red]Potentials, and one for [color:red]Active clients. I want each portal to show Individual's Name and the Company.

Remember, there is one account rep for each company, but the individuals in that company can all have different statuses. So, it could look like this:

Account Rep: Andy

[color:red]

Leads

John Doe Widgets, Inc

Betty Fry Things n' Stuff

[color:red]Potential

Sam Carlson Widgets, Inc.

Joe Gross Boxes R Us

[color:red]Active

Susan Smith Boxes R Us

Kelly Winter Things n' Stuff

How do I set up the relationship so that each employee record only shows the clients belonging to him/her in those categories? I'm running into a problem because I can create a relationship between Account Reps and Individuals (based on Status), and a relationship between Account Reps and Companies (based on Account Reps), but not both. Am I missing some sort of self-relationship or something? Any help would be appreciated.

Link to comment
Share on other sites

Some suggestions...

In a layout for Account Reps, each Rep has their own record (determined by field "Rep Name").

It is probably a better idea to give unique ID numbers to each rep in the event that you have two "John Smiths" in the office.

Status is the status of each Individual (lead, potential, active, inactive)

It sounds like an individual can have only one 'status'. If this is true then 'status' should be a field on the 'individual' table not a separate table of its own.

Attached is a suggested solution...

test.fp7.zip

Link to comment
Share on other sites

This topic is 6066 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.