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
|
|||
|
|||
Table Design
Need opinion,
I hv created Table for Customer Info and Account. My customer has two type..i) Borrower and ii) Guarantor The account table must hv borrower and a guarantor. Question : Can i maintain only one customer table (since info required are the same for Borrower/Guarantor)? My problem is when the two fields in Account Table (Borr ID & Guar ID) are pointing same Cust-ID in CIF Table. Appreciate your help |
#2
|
|||
|
|||
Table Design
On Sun, 19 Mar 2006 18:29:26 -0800, zyus
wrote: Need opinion, I hv created Table for Customer Info and Account. My customer has two type..i) Borrower and ii) Guarantor Can the same person be both a borrower, and a guarantor (perhaps on different loans)? The account table must hv borrower and a guarantor. ANd they must be different people, I suppose? (You don't explicitly say). Question : Can i maintain only one customer table (since info required are the same for Borrower/Guarantor)? Yes, and you SHOULD do so. My problem is when the two fields in Account Table (Borr ID & Guar ID) are pointing same Cust-ID in CIF Table. You can use a "Table Validation Rule" to ensure that they are different. View the table's Properties and in the Validation Rule type [Borr ID] [Guar ID] and in the Validation Text something like "The Guarantor must be a different person than the Borrower, and both are required." John W. Vinson[MVP] |
#3
|
|||
|
|||
Table Design
John,
Add questions 1) Which table shall i put the validation rule . CIF or ACCT table 2) In the ACCT table i hv Borr_ID and Guar_ID, can both pointing to CIF Cust_ID 3) How to retrieve details of CIF in one report where i can see for ex...for ACCT1 Mr A is the borrower and guaranteed by Mr B...(since all the fields are shared in one table) Thanks "John Vinson" wrote: On Sun, 19 Mar 2006 18:29:26 -0800, zyus wrote: Need opinion, I hv created Table for Customer Info and Account. My customer has two type..i) Borrower and ii) Guarantor Can the same person be both a borrower, and a guarantor (perhaps on different loans)? The account table must hv borrower and a guarantor. ANd they must be different people, I suppose? (You don't explicitly say). Question : Can i maintain only one customer table (since info required are the same for Borrower/Guarantor)? Yes, and you SHOULD do so. My problem is when the two fields in Account Table (Borr ID & Guar ID) are pointing same Cust-ID in CIF Table. You can use a "Table Validation Rule" to ensure that they are different. View the table's Properties and in the Validation Rule type [Borr ID] [Guar ID] and in the Validation Text something like "The Guarantor must be a different person than the Borrower, and both are required." John W. Vinson[MVP] |
#4
|
|||
|
|||
Table Design
On Mon, 20 Mar 2006 06:50:34 -0800, zyus
wrote: John, Add questions 1) Which table shall i put the validation rule . CIF or ACCT table ACCT (it seems, bear in mind I don't know your structure). 2) In the ACCT table i hv Borr_ID and Guar_ID, can both pointing to CIF Cust_ID Yes. In the Relationships diagram you would just drag both fields to the CIF Cust_ID. Access will display the CIF table twice, the second instance as CIF_ID. 3) How to retrieve details of CIF in one report where i can see for ex...for ACCT1 Mr A is the borrower and guaranteed by Mr B...(since all the fields are shared in one table) Add the CIF table to the Report's Recordsource query TWICE. Access will alias the second one CIF_1 (you can change this name if you wish); join one instance to Borr_ID and the other instance to Guar_ID. You can use aliases for the fields in CIF - for instance if CIF has a LastName field, you could use BorrowerLastname: [CIF].LastName and GuarantorLastname: [CIF_1].LastName as calculated fields in your query. John W. Vinson[MVP] |
#5
|
|||
|
|||
Table Design
Thanks a lot..
"John Vinson" wrote: On Mon, 20 Mar 2006 06:50:34 -0800, zyus wrote: John, Add questions 1) Which table shall i put the validation rule . CIF or ACCT table ACCT (it seems, bear in mind I don't know your structure). 2) In the ACCT table i hv Borr_ID and Guar_ID, can both pointing to CIF Cust_ID Yes. In the Relationships diagram you would just drag both fields to the CIF Cust_ID. Access will display the CIF table twice, the second instance as CIF_ID. 3) How to retrieve details of CIF in one report where i can see for ex...for ACCT1 Mr A is the borrower and guaranteed by Mr B...(since all the fields are shared in one table) Add the CIF table to the Report's Recordsource query TWICE. Access will alias the second one CIF_1 (you can change this name if you wish); join one instance to Borr_ID and the other instance to Guar_ID. You can use aliases for the fields in CIF - for instance if CIF has a LastName field, you could use BorrowerLastname: [CIF].LastName and GuarantorLastname: [CIF_1].LastName as calculated fields in your query. John W. Vinson[MVP] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Make Table Query - Sorting Errors | Fred | Running & Setting Up Queries | 19 | February 22nd, 2006 09:41 PM |
Seeking some expert advice. | HD87glide | Using Forms | 14 | March 23rd, 2005 10:11 PM |
Table and Relationship design problem | douglas jones | Database Design | 2 | March 16th, 2005 11:45 PM |
unable to repair inobox | Sudheer Mumbai | General Discussion | 1 | February 20th, 2005 11:55 AM |
Update - If statement | Dan @BCBS | Running & Setting Up Queries | 13 | December 14th, 2004 06:02 PM |