Table Design

[zyus]

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

 

[John Vinson]

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]

 

[zyus]

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

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]

 

[John Vinson]

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]

 

[zyus]

Thanks a lot..

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]