Almost there, one form left! (1 Viewer)

[raydan]

I've created a db for my mortage company and I have everything complete except returning customers. I want the customers to have the same customerID but different TransacionID. Sounds simple to me but im having problems. It could be a table issue but im hoping its just a form thing.

I have tables: Customers-CustomerID(autonumber),CustFirstName,CustLastName,PhoneNumber,EmailAddress,OriginatorID.

Then I have Table: Detail- CustomerId(lookup from customer table),TransactionId(autonumber),SSN,Address,City,State,Zip,Spoucename,CreditScore,

Then I have Table: Loan- TransactionId(lookup from Detail),LoanType,LoanAmount,StartDate,EndDate, LoanLender


I have a form for new customers which is the customer table. Then once the customer has his credit checked we enter the customer details with a form. Then when they want to move forword with a loan there is a form that pulls the TransactionID from the Detail Table.

I cant figure a way to make a form that will create a new transactionID associated to the custs CustomerID without overwritting the older record.

What do I need to do?

Thanks for any help.

Scott

 

[Pat Hartman]

I think your table structure needs lots more work. SSN absolutely belongs in the Customer record as do all the other fields in the transaction table. The only reason to have credit score and address in the transaction table is because you think the customer may move and you want to keep a record of his addresses and of course credit score can change frequently so it makes sense to keep it at the time of the loan. Not to mention the fact that you really want the credit score for each customer involved in the transaction. As for Spouse name, you need the same first and last name fields as you do for the primary borrower. Not all spouses share the same surname. Plus calling the field spouse is limiting since not all co-borrowers will be married. My husband and I bought our first house 2 months before we were married.

I think that you really have a many-to-many relationship between transaction and customer. A customer might apply for many loans and a loan may have many borrowers.

I know that a property might have many loans but I'm not sure what your procedures are regarding whether a loan can be secured by multiple properties. This might also be a many-to-many relationship.

And finally, you do need addresses for each customer but do you also need addresses for each property?

To get to your actual question, if you use a form for customers and a subform for transactions and both forms are bound and the master/child links are properly set, you will be able to enter multiple transactions for a customer and Access will populate the linking field in the subform record.