doozeracts Posted November 12, 2007 Share Posted November 12, 2007 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 More sharing options...
Matthew F Posted November 12, 2007 Share Posted November 12, 2007 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 More sharing options...
doozeracts Posted November 14, 2007 Author Share Posted November 14, 2007 Brilliant!! That solves my problem perfectly!!! Thanks for the help. Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 6100 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 accountSign in
Already have an account? Sign in here.
Sign In Now