Relationship Issue

[doozeracts]

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.

[Matthew F]

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

[doozeracts]

Brilliant!! That solves my problem perfectly!!!

Thanks for the help.