473,324 Members | 2,239 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Many-to-Many-to-Many relationship problem

Seth Schrock
2,965 Expert 2GB
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

7 2916
zmbd
5,501 Expert Mod 4TB
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
2,965 Expert 2GB
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
2,965 Expert 2GB
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, 144 views)
Oct 23 '12 #4
zmbd
5,501 Expert Mod 4TB
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
5,501 Expert Mod 4TB
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, 216 views)
Oct 24 '12 #6
Seth Schrock
2,965 Expert 2GB
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
5,501 Expert Mod 4TB
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, 913 views)
Oct 24 '12 #8

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

Similar topics

6
by: David Allison | last post by:
Relationship problem ~ How to tell Owner his db is crap. His db was made in 1999 - 12 tables - none normalised - none with relationships.(YES REALLY) He only knows about inputing data - 22,000...
3
by: musosdev | last post by:
Hi guys I've got the following error on a project which is running locally on a vs2005 machine (built in webserver), trying to connect to my win2k3 server active directory. the error is... ...
6
by: Senna_Rettop | last post by:
Hello, I'm new at Access and ran into a problem. I have a table with a field for customer's names. I want to make a lookup field out of the names by linking it to a table that holds all the...
7
by: Ron | last post by:
Hi All, Using Access2000, winXP. Table 1 = tblClients displayed on frmClients via qryClients. 2nd table = tblInvoices shown on frmInvoices via qryInvoices. 2nd table = tblDetails shown on...
2
by: uarana | last post by:
Hi All, I've been plugging away at this problem for a while now and was wondering if anyone had an idea they could share. I'm working on a document management database that tracks revisions of...
3
by: shawnews | last post by:
Ok...I'll first describe briefly what I've done. Working from a paper form with over 200 fields - broken into 10 areas, I created a database with 10 tables. I then created a form using those 10...
5
by: supershroom | last post by:
Hello all, OK - my problem is that my relationship(s) just doesn't seem to work. I am currently using Microsoft SQL Server 2005, and working through the management studio. For my site coding, I'm...
4
by: PW | last post by:
Hi, I set up a relationship between two tables with the itineraryid fields in both tables: tblDailyItinerary tblDailyMeals I have a form that writes a record to tblDailyItinerary that...
14
beacon
by: beacon | last post by:
Hi everybody, I'm having trouble normalizing or setting up relationships for my tables. Here's what I've got so far: tblPatients: PatientPrimary# (Number) PatientSecondary# (Text) ...
2
by: chrisbo | last post by:
I have three tables and want to create a many-to-many relationship between them. The main table has a composite primary key http://www9.vgregion.se/vastarvet/va/bilder/db_photo.jpg This can't...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.