Many-to-Many-to-Many relationship problem

[Seth Schrock]

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:

Loan Type:
In House
Commercial
Freddie Mac

County:
Benton
Clinton
Fountain
...

Appraiser:
xyz
abc
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?

[zmbd]

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

[Seth Schrock]

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?

[Seth Schrock]

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.

[zmbd]

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). :)

[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

[Seth Schrock]

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?

[zmbd]

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.