Help with Relationships

[Nokia8310]

Please someone help.
I have looked all through these discussions to find a version of my
scenario, but have not been able too. I am using Access 2003.
I am creating a database for a finance company who deal with Mortgages.
I have created the following tables: Contacts, Property, Mortgage, Mortgage
Company, Buildings & Contents, Life Ins, ASU, Investments and Others.
I want to create one form to add all these details into.
ie.. Tabs with all of these names on, so that when you find the client ie
Contact, you can tab across to his Property, Mortgage, Buildings & Contents,
etc.
Each contact can have up to 3 applicants on it. Therefore the following
applies:
Applicants Many to Many Property
Applicants Many to Many Mortgage
Applicants Many to Many B&C
Applicants Many to Many Life Ins
Applicants Many to Many ASU
Applicants Many to Many Investment
Applicants Many to Many Others

Property One to One Mortgage Details
Property One to One B&C
Property One to Many Life Ins
Property One to Many ASU

Mortgage Details One to One Mortgage Co Details
Mortgage Details One to One Life Ins

When I tab through from having identified the client, I need to only see the
mortgage that is associated with that property. Aswell as then seeing all the
other items, ie, life ins, ASU, Buildings & Contents etc
Can someone please let me know how I need to get this up and running?
Many thanx for your help in advance. I would like to stress that if the
reply does not come back in a Dummy answer, I will not be able to understand
it. My knowledge is very limited and my hair is now falling out!
Another issue is that I have read all the books and passages about security
but am still unable create the security to work.
Thats for another day

 

[J. Goddard]

Hi -

You have listed Applicants in your relationships, but there is no table
for them - so those relationships really cannot apply. The applicants
(you say up to three - but will there *ever* be more? What happens if
you get a group of investors?) are part of the client information.

So, as I see it, your relationships might be something like this:

Clients One to Many Property
Clients One to Many Applicants <=== Is this true?Property One to One Mortgage Details
Property One to One B&C
Property One to Many Life Ins <=== Why is this one to many?
Property One to Many ASU <=== Why is this one to many?Mortgage Details One to One Mortgage Co Details
Mortgage Details One to One Life Ins <=== Conflict with above one
Now, can an "Applicant" be a part of more than one client? If so, then
the whole scenario becomes more complicated.

I have indicated potential problems above.

Let'sort these problems out first, then address the form design. In any
database, getting the tables and relationships "correct" is the vital
first step.

John

 

[Nokia8310]

Thanx for the swift reply.
Firstly I shall start by answering the questions you have raised:
I created 3 applicants tables, labelled as follows Applicant 1, Applicant 2
& Applicant 3/Guarantor.
My Contacts Form is the form that has everything in it with the tabs. I
wanted to be able to key in Applicant 1 & 2 and drag them into a Contact but
it didnt seem to work like that. I have it so that the form Contact shows App
1, 2 & 3 on that form and you enter them in there. In this instance, you will
not get more than three applicants.

Clients One to Many Applicants, yes this is true because Client (Contact
really) can have 1, 2 or 3 applicants.

Property One to Many Life Ins, why is this one to many? this is one to many
because when someone buys a house for say £100k, they get insurance to cover
that, but they may remortgage, in which case, they must just top up with
another Life Ins to cover the excess, instead of doing just the one.


Property One to Many ASU, why is this one to many? for the same reason as
the Life Ins.

Mortgage Details One to One Life Ins, Conflict with above one. This should
have said One to Many, as you stated, it would conflict the reasoning I gave
on the Life Ins earlier.

As the database stands, it does the following:
Contact Details shows all three applicants, if there are that many. You can
then tab through to the property for that Contact. 1 Contact could equal up
to 3 applicants.
If those three applicants, as one contact, have another property, then it is
in there as another Contact Id.
I can click through the tabs to Property and only one property would ever
show for that Contact. This has to be the way, or the user would get confused
about which Mortgage he was looking at to which property. I can then go to
the mortgage after the property and just find the details for that property.
Therefore the biggest One To One is Mortgage to Property. There will NEVER be
two mortgages to one property or two properties to one mortgage.

The Life Ins, ASU, B&C, will always be associated to the property/mortgage
aswell. Life Ins is slightly different, as we would only ever record the
amount that they are getting at that time. Ie if they were only getting a top
up, we would just record that in life Ins. Maybe this makes it One to One
really. I think you have made me realise through writing this out, that the
Life Ins would actually always be One to One to property & to mortgage, thanx.

Please please help further for me to continue breaking this down because I
am losing faith rapidly but do not want to do that. I have brazenly told the
bosses that Access is the way to go for that and it is down to me to prove it
to them.
Thanx again for your help so far. Please keep it coming.

 

[J. Goddard]

Hi -

Some more questions and ideas:

I created 3 applicants tables, labelled as follows Applicant 1, Applicant 2
& Applicant 3/Guarantor.

Why would you want three separate tables for the applicants? The data
for each is surely the same, except for the status of one (guarantor).
Q:
What do you do if there is one applicant and one applicant/guarantor?
Leave applicant 2 blank?

Q:
Can there be three applicants, none of which is a guarantor?

Q:
Must there be a guarantor?

(Answers to the above are called "Business Rules", and if you don't have
a proper set of business rules, you can't build what the client wants -
just ask any application developer!)


You could have two fields for each applicant in the contact table, one
for each applicant ID and another for the status = Applicant or
Guarantor, then have only one table for all the applicants. Having
three separate tables makes keeping applicant information current a
nightmare (the same applicant could be in all three tables).

I don't really like the above solution - it can lead to all sorts of
problems. The structure I favour is:

Take the applicant information out of the client table.
Create another table which has, as a minimum, these fields:

ClientID
ApplicantID
Applicant Status

Clients One to Many Applicants, yes this is true because Client (Contact
really) can have 1, 2 or 3 applicants.

No, it's not one-to-many. It's one-to-one with up to three different
tables. See my comments above.

If those three applicants, as one contact, have another property, then it is
in there as another Contact Id.

This makes the clients to property relationship one-to-one.

Is is really one-to-one? It is one property to one CURRENT mortgage,
yes, but what about refinancing, which happens often. Now you have a
new CURRENT mortgage, but also historical one as well. The new one
might not even be with the same mortgage company. You most certainly
want to keep the data for the first one, too. So, from a database
structure point of view, we have a one-to-many. Date fields could be
used to determine which is current, or you could use a status tag.

Therefore the biggest One To One is Mortgage to Property. There will NEVER be
two mortgages to one property or two properties to one mortgage.

Not having two properties to one mortgage I can understand - but can a
property not have a second mortgage on it sometimes? It's quite common
in the residential world.



Of these two, only the second one is right; the life insurance applies
to the mortgage, not the property. Is the Life Insurance one-to-one
with the mortgage? I can't really answer that, because I'm not in the
finance industry. But, if the details of the insurance can change
significantly over the life of the mortgage (e.g. can the insurance go
to another company?), then it will be one-to-many too, for the same
reasons as property to mortgage might be one-to-many.

I guess the same might be said for ASU - but I've never heard of it -
what does ASU stand for?

As I said earlier - get the database structure sorted out first, only
then can you start thinking about forms.

(Actually, one should get the get the database structure and
relationships sorted out before the database is even built. It's
incredible the difficulties people get into because they didn't take the
proper first steps - but that's another story.)

Keep at it - you'll get there.

John

 

[Nokia8310]

John,
Thanx again for your help, although I'm beginning to wonder if you maybe
wasting your time. My intelligence is high but my patience is not, with this
database.
I do not understand some of your technical jargon. I am but a simple man
when it comes to Access and it seems you are aiding to show me the errors of
my way very well but I am finding it dificult to master your ease at
explaining what seems very simple to you but incredibly hard to me. Let me
try to explain in reference to your raised questions.

Q. Why would you want three separate tables for the applicants? The data
for each is surely the same, except for the status of one (guarantor).

I purely set up three tables because I was previously told too in here but a
guy that didnt seem to have any patience for me and made me feel like a jerk.
Although I set the three tables up, they have no data in them. The Form
Contacts Details is the main form and has all the other forms attached to it
via tabs.
In the Contacts Form i have the following:
Contact Details Id
Applicant1 - free field just to title app1
title1
first name1
middle name1
surname1
date of birth1
age1
name/number1
road/street1
place1
city1
county1
post code1
home tel1
mobile tel1
smoker1
e mail address1
notes1
notes1a
notes1b
Then I have the same but with 2 or 3 after it for the other applicants.
I hope that makes sense.

Q. What do you do if there is one applicant and one applicant/guarantor?

Leave applicant 2 blank?

Yes but it would be very rare indeed. The norm is one app, or two apps per
case.

Q:

Can there be three applicants, none of which is a guarantor?

Yes there could be, there also could actually be four apps, although very
rare indeed but is possible. I chose 3 apps because I went through historical
cases and we never had a case with 4 apps at all. It was also easier for me
to make the form a nice size with just the 3 apps on it.

Q:
Must there be a guarantor?

No there must not. A guarantor would be very rare indeed. You can only ever
have 1 Guarantor per case but it would generally be with 1 app or 2 apps. The
terminology of Applicant3/Guarantor was just as a heading.

Clients One to Many Applicants, yes this is true because Client (Contact

No, it's not one-to-many. It's one-to-one with up to three different
tables. See my comments above.

I am struggling to understand how this is. My Contacts form has tabs in it
that I have copied and pasted the data into from their respective forms, ie
Mortgage Details has the Mortgage Detail Form data copied across. Therefore
there are numbers at the bottom of each page showing that you could go to the
next record. The next records are significant for the Contact Details because
you can then filter to find the right applicant (this is another issue. One
of the users has managed to make the info not visible in the Mortgage Details
form view when filter by form is selected. But if i hit design view and then
click back, the info is there???). I wanted it that once you have selected
the correct contact, ie app1 John Smith, app2 Joan Smith, you could then
click property tab and find the property involved ie 123 St John Street. One
property to one contact. Therefore John & Joan Smith can be in the database
as many Contacts but the individual denominator would be the mortage. Yes
they could have a mortgage to 123 St John Street before but in the Mortgage
Details there are a lot of fields that state dates and completion. That is
the way we are showing historical cases.
Hope again that this makes sense.
I think above answers your questions

Property One to One Mortgage Details
Is is really one-to-one?

Not having two properties to one mortgage I can understand - but can a
property not have a second mortgage on it sometimes?

By the way ASU (sorry) stands for Accident, Sickness & Unemployment.

I feel like I am now struggling even more because it sounds from what you
have told me that I have done this completely wrong. This is not a slur
against you but a rejected feeling towards me. I have played with Access to
create this database and managed to get it to do what i wanted it to do BUT
it sounds like it isnt right.
Once again, I want to thank you extremely for your help so far, you have
given me a lot of time and i dont want to feel like i am putting on you. I
need to get this sorted but am now believing that it is not the way to go
forward. I think I may need to get someone into to design this in the way
that it needs to be done.
Thanx again John and hope that some of my answers have helped for you to
understand the complexity of my issue.