Mr & Mrs (1 Viewer)

[Sc0tt1e]

I have 2 tabs within a form pulling info from the customer table.


How can I determine a Mr & Mrs relationship to populate names correctly (i.e. sole application so Mr only or joint application so Mr & Mrs)?

 

[theDBguy]

Hi. Welcome to the forum. How would you normally determine that visually or manually? If you can give us more information or post some images, maybe we can translate the manual method into code.

 

[Micron]

would have to see records that contained that information or have some idea as to what would associate Mr or Mrs with a partner/spouse.

 

[Sc0tt1e]

First time using Access as a developer. I'm trying to work out how to beuild a CRM system for my mortgage clients.


I have built the back end and am now exploring forms. As i say I have 2 tabs each pulling from the customer table, my question is what would I need to do to provide something for a relationship to be determined.



I've wracked my brain but am too new to the format and have hit a wall with the videos available. There must be an existing method to associate 2 people within an access table, I just cant find it.

 

[Micron]

hate to have to say it but rephrasing your question without providing the information asked for leaves us (at least me) exactly where I was - clueless as to what to suggest.

It seems odd that you have 2 tabs (pages in a tab control??) looking at the same info regardless.

 

[June7]

I don't find 2 tabs odd. This is a practical way to organize customer info to reduce size of form. Say one tab shows address info and another tab shows some other details. All from the same record. So depends what they mean by 'pulling'.

You need fields that identify relationship. What if the individuals have domestic relationship but do not use titles of Mr. and Mrs.? What if they don't have domestic relationship but still apply jointly? Simplest approach is to disregard marital status and just use names in addresses.

 

[theDBguy]

First time using Access as a developer. I'm trying to work out how to beuild a CRM system for my mortgage clients.

I have built the back end and am now exploring forms. As i say I have 2 tabs each pulling from the customer table, my question is what would I need to do to provide something for a relationship to be determined.

I've wracked my brain but am too new to the format and have hit a wall with the videos available. There must be an existing method to associate 2 people within an access table, I just cant find it.

Hi. Since we can't see what you're looking at, it would be helpful to give us some verbal descriptions of exactly what you have and tell us how you would determine, as a human, if two clients are related. We can then translate it into a database way. For example, if you say I have a field called FName with the name of the client and another field called SName with the name of the spouse, then we can tell you how to look into those fields, using code, to determine how to address the client.

 

[Sc0tt1e]

As I said, Customer table has customer details, Currently both tabs of Customer Form are being completed using the same customer details. I am trying to work out what I need to do so I can create a relationship between various rows in the customer table.


Mr and Mrs may have same surname but I can have 2 Mr at different addresses on the same mortgage application because they are both company directors. I need a way of letting Access know customer ID 97 is linked to the mortgage as is customer ID 587 and 97 is to be completed in Tab1 (Primary customer?) and 587 is tab 2 (secondary customer?)

 

[MajP]

I am guessing here. I would assume however you need a junction table to make a many to many.

tblCustomerLoans
  customerID_FK  
  LoanID_FK
 'other fields uniquely relate a customer to a loan

This way a customer can be related to many loans and a loan can be related to many customers. Then get rid of the customerID in the loan table.

 

[Sc0tt1e]

I am guessing here. I would assume however you need a junction table to make a many to many.

tblCustomerLoans
  customerID_FK  
  LoanID_FK
 'other fields uniquely relate a customer to a loan

This way a customer can be related to many loans and a loan can be related to many customers. Then get rid of the customerID in the loan table.

That makes sense when relating customers to a loan, but doesn't tell Access who should go in which tab of the form?

 

[MajP]

You could probably make that work with a lot of code, but that is not a very traditional design. You would likely have to do it unbound, or write code to move to the second related customer record when you switch tabs. It can be done, but would not recommend it for a beginning db.

Normally this would be done with a Main form and subform. So the main form is LoanDetails and the subform would be Customers in a continous view. Then you relate customers to loans. This can get complicated to do all on one form for adding, editing, and navigating. I would likely have a seperate form to add a new customer which could be used as pop up. If the customer is already in the DB then you select them from a list to add to the junction table. If not you can pop open a form to add a new customer then add them to the junction table.

Since this is a many to many you may want to have options to pick a customer and see all of their loans or pick a loan and see all of its customers.

 

[Mark_]

First part is "How to relate two records". As this is for a mortgage that means you can have one OR MORE names on title/what have you, especially if dealing with business partnerships. That means you need to have a 2nd table that allows you to link individuals together.

The linking table is rather straight forward. Copy of record 1's primary key, copy of record 2's primary key, and a "Relationship" field to identify your Mr/Mrs/Partner/what not.

As to what goes on what tab? You would normally have ONE record being dealt with on one form, so you'd never have a "tab for the 2nd record", but you may have a tab with a subform that looks at a DIFFERENT table, say the linking table, and then shows other records your customer is linked to.

This will allow you to have John Jr. as your customer, but links to John Sr. and his wife Jane. John Sr. and Jane are co-signers for John Jr. who is John and Jane's son.

 

[MajP]

Another thing to consider is two people can have the same address (I will assume primary address to a person does not have many addresses). Therefore the foreign key should be in the customer table not in the address table. The customer table should have an address FK. Mr Smith and Mrs. Smith would have the same FK (assuming they live together). But there is a lot more relational mistakes.

You need to stop worrying about your forms and get your table design correct. Your tables do not make sense and it may be a little more complicated with some more many to many. Come back once you have your tables correctly related. Also get rid of ALL spaces in table and field names and get rid of special characters.

Couple of examples with relations.
1. Customers are related to loans. Property are related to loans. Therefore Property should not have customerID in it should go through the loan since a loan has a property ID.
2. In db design you never have complimentary foreign keys. You have a propertyID in the Loan table and a loanID in the property table.

 

[Gasman]

That makes sense when relating customers to a loan, but doesn't tell Access who should go in which tab of the form?

So one client is marked as Primary, the second as Secondary.?

 

[gemma-the-husky]

I would have thought, if you have a joint application, you might set up two "applicant/customer/client" records, but the "application" record would show a main applicant, and a co-applicant.

De-normalised, but I expect this is the way it's normally done.

 

[Sc0tt1e]

I have got this (attached) far but I cannot work out how the Customers/Loans/Property can be linked up. Multiple of all???


Customers can have multiple properties
Customers can have multiple loans
Properties can have multiple Loans
Loans can only have one property

 

[theDBguy]

I have got this (attached) far but I cannot work out how the Customers/Loans/Property can be linked up. Multiple of all???

Hi. How exactly are those tables related? I don't see a CustomerID in either Property or Loan tables.

 

[Sc0tt1e]

Hi. How exactly are those tables related? I don't see a CustomerID in either Property or Loan tables.

At the moment they aren't as I cant work out how it should be done

 

[theDBguy]

At the moment they aren't as I cant work out how it should be done

Okay, so rather than figure that out, why not tell us how, in "real" business scenarios would any of them relate to each other. We can either tell you if you need another linking table or simply merge the tables together instead. Or maybe, you just need to a foreign key to one of the tables.

 

[Sc0tt1e]

Okay, so rather than figure that out, why not tell us how, in "real" business scenarios would any of them relate to each other. We can either tell you if you need another linking table or simply merge the tables together instead. Or maybe, you just need to a foreign key to one of the tables.

Customers can have multiple properties
Customers can have multiple loans
Properties can have multiple Loans
Loans can only have one property