Creating relational databases

[Gail]

I already have a relational database between two tables (table A and table B)
and am trying to create a relationship between table B and C. Table A has
all client details (ID key, name, address, phone, birthdays, etc.) and Table
B has loan details (ID, ID key from table A, loan amount, lender, loan
number, settlement date, date commission received, etc.). What I would like
to set up is for Table C to have a list of the commission received each month
for each loan number (one - "B" to many - "C" ??). At the moment I have the
commission info in a spreadsheet. I am doing something wrong as it wont work.

 

[Jeff Boyce]

Gail

"...it won't work..." doesn't give us much to go on.

It sounds like you understand the use of 1-m relationships. What is in your
"TableC" info that ties that table back to TableB?

Where are you trying to do something between Tables B & C?

 

[Klatuu]

Assuming commissions are paid against a specific loan, then relate your
commissions table to the ID in Table B. (ID, ID key from tabe B,...)
Also, there is a design flaw in table B. The lender should not be in the
loan table, there should be a lender table. That is because it is highly
unlikely each loan has a unique lender. If a lender appears more than once
in your loan table, you have redundant data.

 

[Bob Miller]

As far as I can tell, you don't need a third table as you can do what
you want in a query of table B. The easy way to do it would be to use
the Query Wizard to query B. Select the loan number, the date
commission received, and commission fields and select Summary options.
Select Sum for the commission and then at the next screen select Month.

 

[Gail]

As I see it, each loan does have a unique lender - John Smith has loan number
123456 for $100,000 with XYZ Bank as the only lender. Commissions are paid
monthly against a specific loan number. I did manage at one point to get the
1-many but I feel it was the wrong way round as the loan table was the 'many'
and the commission table was the '1' - whereas what I need is '1' loan with
'many' commissions. Can I change the structure of a current table that has
been in use for a couple of years.