Hi people, I have been pondering over this matter for awhile and i still cant find a solution so i hope they you all have one. So i am new to access and i have done some self studying on my own about design basic and normalisation(although i wont following this strictly due to the nature of this database im about to build) and i understand the concepts but i cant apply this to my database. Here is the case: My database has 9 tables: Borrower Detail,Roles and Doc,Type of Security, Repayment,fee,checklist,regulatory submission,majority lenders,required accounts. The thing about this database is that i want all the tables to be dependent only on borrower details (e.g without borrower details, there will be no info in any tables). It is a one-to-many relationship with one being the borrower detail table and many being either of the remaining 8 tables. So the way i want to enter information in this database is using a form. First i will enter new borrower details in a form and then i will enter info into the rest of the tables(also using forms) based on each new record i entered .So can you give me a clear solution on how to define the relationship properly and also how can i add info in the 8 tables in such a way that the info i entered will be based on a specific borrower record.(e.g. in the Roles and Doc form, i will select the borrower ID first so that Access understand the info will be linked to this specific ID).
Urgent Question about creating relationships among tables
- Thread starter xwnoob
- Start date
Welcome to the forum!
Not without more detailed information about YOUR data and YOUR business process...
For example, can a specific role/doc apply to more than 1 borrower?
Can you provide an example of role/doc?
Can a type of security apply to more than one borrower? example data?
What is meant by majority lenders? Can a lender lend to more than one borrower?
Similar questions would apply to your other tables as well and how they relate to borrowers as a whole.
BTW, it is best not to have spaces or special characters in your table and field names and also make sure not to use reserved words in naming tables and fields.
Not without more detailed information about YOUR data and YOUR business process...
For example, can a specific role/doc apply to more than 1 borrower?
Can you provide an example of role/doc?
Can a type of security apply to more than one borrower? example data?
What is meant by majority lenders? Can a lender lend to more than one borrower?
Similar questions would apply to your other tables as well and how they relate to borrowers as a whole.
BTW, it is best not to have spaces or special characters in your table and field names and also make sure not to use reserved words in naming tables and fields.
MSAccessRookie
AWF VIP
- Local time
- Today, 05:39
- Joined
- May 2, 2008
- Messages
- 3,428
This problem could probably be resolved by making the Foreign Key to the Borrower Detail Table a Required Field in each of the other Tables. In that way, no record could be entered in any of the Tables unless there was first a matching entry in the Borrower Detail Table.
You should also consider the points raised by jzwp22. They are all valid concerns and will probably need to be addressed sooner or later, so why not sooner.
Last edited:
Hi thanks for replying,
I have made sure not to use spaces in the table/field so i guess thats fine.
About the information, tbh i am not sure about the process/data as i'm an intern in my office so im not familiarise with it yet but im suppose to build a database.What i can tell you is that every table is connected only to the borrower detail table having a one-to-many relationship(e.g.you asked if a specific role/doc can apply to more than 1 borrower? no to all the question you asked ) ok let me try to clear things up: For this moment just forget the names of the table . All information i enter in other tables beside the borrower table will only be concerned with one borrower. These information such as fees or checklist will not be related to other borrower. a lender that is lending money to borrower 1 will not be related to borrower 2 (i know its weird but thats the best way i can explain)
You can have as many types of security as you want as long as it is tied to a borrower detail record( I am entering data into forms based on a single borrower detail record). Besides the borrower details table, all the tables are not related to each other at all .
Actually i have started on the database the only part im stuck is the relationship part. I will try to explain clearly what it does: First, user will enter new borrower details record into a form. Then, using the Borrower ID i generated(i used autonumber to do this), users then can enter information in other tables based on this ID. For e.g. i enter new borrower record in a form and the ID is 001. Then i want to enter new fees for a borrower in a form so first i select this ID in a option box in the fees form and only then will i enter the new fees based on ID 001. You can enter as many fees as you want and many lenders as you want but all are based only on Borrower ID 001
So just to sum things up i will enter information based on a single borrower and this is considered as one case for me. So this is why lenders cannot lend to more than one borrower because they are considered seperate cases and borrowers and lenders in case 1 are different from borrowers and lenders in case 2.
i Hope i gave enough info as if i were to explain about the data i think i will be confused even more.
Assuming that all of the other tables have one-to-many relationships with the borrowers table then the best approach is to define those relationships in the Relationship Window in Access prior to creating the forms.
In terms of forms, you will want to create a main form based solely on the borrowers table. Then create individual forms based on each of the related tables. Drag and drop each of those forms into the main form. Access will automatically link the main form with each of the now subforms. That way when you add a record to the main form and then move to each subform that information will automatically be tied to the record shown in the main form.
To help organize the subforms a little, it would be best to use a tab control and then place each subform within its own tab.
In terms of forms, you will want to create a main form based solely on the borrowers table. Then create individual forms based on each of the related tables. Drag and drop each of those forms into the main form. Access will automatically link the main form with each of the now subforms. That way when you add a record to the main form and then move to each subform that information will automatically be tied to the record shown in the main form.
To help organize the subforms a little, it would be best to use a tab control and then place each subform within its own tab.
yes i created a switchboard to go to different forms based on related tables. However can i do it a different way as im not so sure about sub-forms. Is it possible to have an option box on each of the forms to choose the borrower ID and then add in the data which would be tied to this ID?
P.s. are the subforms that you mentioned inside the main form or they are just individual forms that you just refer to as sub-forms? because i have heard of sub-forms and saw some examples (the ones that are inside a form)
P.s.s. Sorry but let me ask you one more thing: So another field that borrower details has is borrower name. Must i put this field inside the other table(e.g. put borrowername inside lenders table) if i want to have it inside the option box? I want to let the user see the ID as well as the Name and then select it. But would this borrowername count as a foreign key? Because its not the primary key of Borrowers table as ID is the primary key.
Last edited:
Subforms are just regular forms that become subforms when you drop them into a main form. The main form is usually based on the table making up the one side of the one-to-many relationship while the subform is based on the table making up the many side of the relationship.
Subforms are commonly used, so it would be best to come up to speed with them. Access does make it easy as long as your relationships are defined in the Relationship Window ahead of time.
Of course, if you do not want to use subforms, you will have to identify the pertinent borrower's primary key value and pass that to the subform as you open it. You might have a form that shows the borrower (single form view) and on that form have a button or series of buttons that open the form/forms for the detailed info (the many side), in opening the form you would pass the primary key value to the form you are opening. This would take a little Visual Basic for Application (VBA) coding similar to this.
Code:
DoCmd.OpenForm "NameOfFormToOpen", acNormal
Forms![NameOfFormToOpen]![NameOfControlThatWillHoldTheBorrowerID] = me.NameOfControlOnFormThatIsAlreadyOpenThatHoldsBorrowerIDIn the code snippet above the "me." is a shorthand notation that refers to the current form where the button is located.
This should work OK if you only enter 1 record in the form that opens; if you try to enter a second record the ID value might not carry over to the next new record. To handle that possibility, the form you open would have to be a main/subform design with the only 1 control in the main form (the one holding the ID that was carried over).
The general rule is that the keys (primary and foreign) should have no significance to the user (and they should not see it). The primary key is just a way for the database to uniquely identify each record. If the borrower has an ID value of some sort such as a social security number, then that would be a separate field.
The borrower name field should be in the borrower table and only that table. You would use the key value applicable to the borrower in the lender table (and the other related tables). If you want to see both the borrower name and social security number, you would still include the ID value in the list/combo box (you would hide the ID value from the user)
- Local time
- Today, 09:39
- Joined
- Sep 12, 2006
- Messages
- 16,127
the highlighted statements you made.
sorry - you just cannot say this. The table design needs to be what it must be. you cannot arbitrarily pre-determine that all tables WILL be dependent on another particular key table,. They may be (although I think not in this case)
eg among other things ....
how CAN the type of security possibly be dependent on the borrower?
how CAN the fee possibly be dependent on the borrower?
how do you intend to deal with multiple applicants?
how do you deal with multiple aplications by the the same party.
in my opinion the key document must be the application itslef, but it still does not mean that every other table bears a direct link to the application table. Clearly it does not.
I think you really need to rethink this, understand normalisation, and apply it correctly.
