I have an Access 2007 database that I built to keep up with clients that I have enrolled within a credit repair company that I am currently working for. My intent is to be able to pull reports or queries with not only the client’s info, but also who referred them. Sometimes the client is a referral from a loan officer and a realtor, or a loan officer, realtor and home builder. As of now I have a client table, mortgage professional (MP) table and a mortgage company (MC) table. The MP table is linked to the client table by the MP ID, which is also the primary key and the MC table is linked to the MP table by the company Id, which is also the primary key. I have thought about creating another table and separating the loan officers from the realtors and home builders, but then this might cause a problem when linking the two tables to the MC table. The problem that I am having is when I run a query the first referral works properly only with no null values “which is another issue” and the second referral pulls data that is identical to the first referral column. Within the MP table I have assigned three different identifiers for each person, only one is the primary key because the first referral column will never have null values. Occasionally there will be a second referral, so the second referral column will have null values. When I joined the relationships I linked MP Id to first referral within the client table and the second referral from the MP table to the second referral within the client table. I would be happy if I create queries or reports that would show me all of the referrals for each client, this could be up to three, but I can manage with only two because clients that have three referrals does not happen very often.
There's a flaw in the database structure you describe and it is not normalized. There should only be a single table for all "people" with a column indicating their quality (loan officers, realtors, home builders, etc.) . You should then use junction tables (aka bridge tables) to create many-to-many relationships.
Thanks for your help, I am going to attach a print screen of my relationship diagram. I not sure how to use the junction box because sometimes there will be null values in the referral columns within the client table until I can impute this info. I believe I need to have a junction table between the client and MP table, any ideas.
Last edited by richardfarrar; 08-22-11 at 15:21.
Reason: After researching links my question is Irrelevant
Are you telling me that I would have to include all people (client, LO, Realtor, builder) within one table?
If the structure of information you store for all categories (client, LO, Realtor, builder) is the same or is easy to uniformize, then yes you should, or at least that's what the rules of database normalization prescribe. The cardinal principle here is that you do not store the same kind of information (= same data structure) in more than one place (table).