If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help with Relationships
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 |
#2
|
|||
|
|||
Help with Relationships
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 wrote: 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 |
#3
|
|||
|
|||
Help with Relationships
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" wrote: 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 wrote: 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 |
#4
|
|||
|
|||
Help with Relationships
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. Property One to One Mortgage Details 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. Property One to Many Life Ins Mortgage Details One to One Life Ins 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 wrote: 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" wrote: 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 wrote: 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 |
#5
|
|||
|
|||
Help with Relationships
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 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. 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. "J. Goddard" wrote: 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. Property One to One Mortgage Details 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. Property One to Many Life Ins Mortgage Details One to One Life Ins 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 wrote: 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" wrote: 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 wrote: 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 |
#6
|
|||
|
|||
Help with Relationships
Hi -
I have attached my latest ideas for you in an MS Word document. Please don't think you are wasting my time - I like helping others out. From some of your descriptions, if sounds as if maybe you are trying to make MS Access look and act the same as a current paper-based system. IMHO, this approach rarely works well. I will give some thought as to how you can put a data entry form together. If you wish to communicate with me directly with E-Mail, I'm OK with that - take the NO_ _SPAM stuff out of the address. Take care - don't get too frustrated. Getting used to MS Access and all it can do takes a while. John Nokia8310 wrote: 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 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. 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. "J. Goddard" wrote: 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. Property One to One Mortgage Details 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. Property One to Many Life Ins Mortgage Details One to One Life Ins 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 wrote: 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" wrote: 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 wrote: 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 |
Thread Tools | |
Display Modes | |
|
|