By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,454 Members | 2,633 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,454 IT Pros & Developers. It's quick & easy.

Many-to-Many-to-Many relationship problem

Seth Schrock
Expert 2.5K+
P: 2,930
I am currently working on the design stage of a new database. One particular part is giving me problems. The database is going to keep track of loans and their appraisers. The information that I need to link is this:

Expand|Select|Wrap|Line Numbers
  1. Loan Type:
  2. In House
  3. Commercial
  4. Freddie Mac
Expand|Select|Wrap|Line Numbers
  1. County:
  2. Benton
  3. Clinton
  4. Fountain
  5. ...
Expand|Select|Wrap|Line Numbers
  1. Appraiser:
  2. xyz
  3. abc
  4. def
My problem is that each appraiser is approved for multiple counties and each county has multiple appraisers (first many-to-many relationship). Each appraiser can do multiple types of loans and each type of loan has many appraisers that can do it (second many-to-many relationship). The trick is that the type of loan that an appraiser can do is dependent on the county. So appraiser xyz can do all loans in Benton county, but only In House loans in Fountain county and only Commercial loans in Clinton county. How do I set this up?
Oct 23 '12 #1

✓ answered by zmbd

Just a few tables.
Most of them are basic lookup types.
I've entered a brief description about each table in the properties.
tbl_authorizedloanclass... this is the magic
one record per appraiser per county per loantype
so... xyz, benton, in house
and... xyz, benton, commercial etc... so right now, XYZ can only write two types of loans and only in the one county.

The form (the only form) handles all of the drudgery for creating and maintaining loans against the appraisers... the cbo-ctrls are cascading

In the database tools there is a database documenter if you want all of the gory details.

-z

Share this Question
Share on Google+
7 Replies


zmbd
Expert Mod 5K+
P: 5,285
Try the three tables as given.
Table with unique loan record
Table with authorized loan types per officer (type and county)
Table linking the loans to officers

I have an instrument down in one of the remote labs so give me a couple of hours to get back to this and I'll see if I can't bodge togeither an exampler DB
Oct 23 '12 #2

Seth Schrock
Expert 2.5K+
P: 2,930
Something that I should have said to begin with... How these tables will help me is that when we enter a loan, we will select the loan type and the county. I then will have a combobox that will list the appraisers. The list of appraisers will be filtered down by those two fields so that only the appraisers that can do it are available.

@Z what is the design of the second table you listed?
Oct 23 '12 #3

Seth Schrock
Expert 2.5K+
P: 2,930
Here is an idea that I had, but I don't see how it will work. I included the main form. I just don't have the WHERE clause in the Appraiser combobox.
Attached Files
File Type: zip LoanTrackingTesting.zip (34.0 KB, 87 views)
Oct 23 '12 #4

zmbd
Expert Mod 5K+
P: 5,285
Be patient... I almost have it...

I had a major instrument go down in one of my main labs so I just got a chance to sit down and take a look.

Tables and queries are done

Just finishing the logic behind the form for new accounts... be this evening. I have a meeting and some associated responsibilties at the Church first thing this evening then I'll get it posted... be after 21h00-CST (03h00GMT). :)
Oct 23 '12 #5

zmbd
Expert Mod 5K+
P: 5,285
Just a few tables.
Most of them are basic lookup types.
I've entered a brief description about each table in the properties.
tbl_authorizedloanclass... this is the magic
one record per appraiser per county per loantype
so... xyz, benton, in house
and... xyz, benton, commercial etc... so right now, XYZ can only write two types of loans and only in the one county.

The form (the only form) handles all of the drudgery for creating and maintaining loans against the appraisers... the cbo-ctrls are cascading

In the database tools there is a database documenter if you want all of the gory details.

-z
Attached Files
File Type: zip bytesthread_943879.zip (67.1 KB, 143 views)
Oct 24 '12 #6

Seth Schrock
Expert 2.5K+
P: 2,930
After playing around with your sample database and seeing what I can do with it, I think that I like the design and I think that I understand most of what you have done. I do have a couple of questions though:

1. Why the three relationships between tbl_authorizedloanclass and tbl_loanaccounts?

2. Why do you have a separate section of the form devoted to adding records and have the main portion of the form only allow viewing?
Oct 24 '12 #7

zmbd
Expert Mod 5K+
P: 5,285
So that everyone can see what we're talking about:



1) If you click on one of those triple links and try to edit you'll see that they open in the same relationship dialog. By doing this I'm treating them as a compound primary key in tbl_authorizedloanclass and as Foreign key in tbl_loanaccounts.

>>>EDIT>>> And in fact the three fields in are part of a compound index in tbl_authorizedloanclass set to unique but not primary - thus you can not have two records using the exact same apprasier, county, and loan type<<<Edit<<<

The Extra "pk_*" in each is a bad habit I have... compound keys are good for this but a pain in VBA (i know... breaks a zillion rules) Thus you get the required "related record."

2)Well, my thought is the top section is for reviewing the created accounts with a limited means of editing. I would think that one would not want the user easily modifying accounts once assigned to an appraiser.

I could attempt the cascading effect there; however, I've occationally had issues with bound controls when I've done so in the past.

The bottom section.... because I liked it ;-P
and it allowed the new record creation to be always available while avoiding the issues I've ran into with bound controls when attempting the same affect.
Attached Images
File Type: jpg bytesthread_943879_relationships.jpg (26.5 KB, 619 views)
Oct 24 '12 #8

Post your reply

Sign in to post your reply or Sign up for a free account.