Question about foreign key if more that one field is placed in another table

[xwnoob]

Hi,

Is it possible to have more than 1 field in a table placed into another table as part of a relationship? If so, if you put two fields:borrowerID and borrowerName into Lenders table, must both of them be primary keys? Assuming borrowerID is a primary key of a table called Borrower.

[pdebaets]

BorrowerName should go in the Borrower table.

If you need to create a report that shows BorrowerName with the associated lender's information, then first create a query that joins the Borrower and Lenders tables, then use that join query as the record source for your report.

[June7]

Agree. The point is, don't duplicate data. There is no reason to save the BorrowerName to Lender table. Retrieve the related data by joining tables in query on the key fields. That's what they are for.

[xwnoob]

BorrowerName should go in the Borrower table.

If you need to create a report that shows BorrowerName with the associated lender's information, then first create a query that joins the Borrower and Lenders tables, then use that join query as the record source for your report.

Can you explain to me which what query should i use? For my case i want to do it on a form : The user selects a BorrowerID(Displayed in a option box allowed with the name) and then updates Lender information based on the ID.

[June7]

The form is bound to the Lender table? Option box (a combobox?) is bound to the BorrowerID field of Lender table? Done.

The query joining tables would be needed in a Report so that the related BorrowerName could be displayed.

[xwnoob]

The form is bound to the Lender table? Option box (a combobox?) is bound to the BorrowerID field of Lender table? Done.

The query joining tables would be needed in a Report so that the related BorrowerName could be displayed.

Lol alright i understand what your previous post is trying to do which seems more difficult than what im requesting now hah.

EDIT:What about displaying the borrower name in the combo as well? How do i do it since i have no borrowername field in the Lender table?


Now im reading more on queries now than you mentioned it which may be useful in the future. I guess ill try to do it first instead of visualising everything.

[pdebaets]

First, is there a many to many relationship between Borrowers and Lenders? Can you have a Lender with more than one Borrower? Can you have a Borrower who borrows from more than one Lender? If the answers to these last two questions is yes, then you have a many-to-many relationship and that requires a LenderBorrowers table, linking the two.

[xwnoob]

First, is there a many to many relationship between Borrowers and Lenders? Can you have a Lender with more than one Borrower? Can you have a Borrower who borrows from more than one Lender? If the answers to these last two questions is yes, then you have a many-to-many relationship and that requires a LenderBorrowers table, linking the two.

its a one to many relationship one borrower can have many lenders but thats not my concern right now its about the combox box as ill be quite happy if i solve this one for now.Hopefully June or any else can help me to find a solution asap as im stuck in a rut right now lol

[June7]

Use a multi-column combobox for the BorrowerID and Name. Review this tutorial http://datapigtechnologies.com/flash...combobox3.html

[pdebaets]

OK, just checking. By far the most important thing to do is to get your tables and relationships in order before proceeding with form design.

[xwnoob]

Use a multi-column combobox for the BorrowerID and Name. Review this tutorial http://datapigtechnologies.com/flash...combobox3.html

Thanks for the video. So just to clarify as long as i dont change the bound column in data type and my 1st column is borrowerID(and i can put any field in the second column and it would affect outcome ), the new lenders data i enter will always be based on the borrowerID i selected right?

[June7]

'would [NOT] affect outcome' - aside from that, think you have correct understanding