Many to Many (AGAIN!)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've reviewed a variety of posts and have gotten so confused it's almost
laughable.

I'd appreciate some help here.

I have a Borrower table and a Loan table. One borrower can have many loans,
one loans can have many borrowers.

tblBorrower
BorrowerID (what type should it be?) I have it auto numbered at the moment
LName
Fname

tblLoan
LoanNbr - Text - PK (but can't be a PK since it can't be null....)
LoanAmount

tblLoanJunction
LoanJunctionID

I created a Junction where the BorrowerID and LoanNbr are the PK
Then in the relationship window created a one-many between the tblBorrower
BorrowerID and the tblLoanJunction BorrowerID and a one-many between the
tblLoan LoanNbr and the tblLoanJunction LoanNbr

This obviously doesn't work but I can't figure out why!

Any help here?
 
tblLoanJunction has the following
LoanJunctionID - AutoNumber
LoanNbrID - (tried to change type to Text but won't let me. Says I need to
delete the relationship in the Relationship Window...which I have...then
compacted the db and still it thinks there's a relationship somehow)
BorrowerID - Number - Long Integer
 
tblBorrower
BorrowerID [autonumber] (PK)
LastName
FirstName

tblLoan
LoanID [use autonumber] (PK)
LoanNumber [text, use whatever format is required by company]
LoanAmount

tblBorrowerLoans
BorrowerID (PK)
LoanID (PK)

Join the two BorrowerID's and the two LoanID's in the relationship window.
 
That's better however you stated earlier:
tblLoan
LoanNbr - Text - PK (but can't be a PK since it can't be null....)

I don't understand what you are suggesting. Either it's a PK or not. Being
text shouldn't have any affect on the ability to be a PK.

The LoanNbrID in tblLoanJunction must be the same data type as in tblLoan.
 
Just to add a note to the discussion, related fields need to be the same
data type (e.g. Number) as Duane already pointed out, except that if one
field is Autonumber the field to which it is related needs to be Number
(Long Integer is a good choice).
It should be OK to use Autonumber for a PK.
 
if one
field is Autonumber the field to which it is related needs to be Number
(Long Integer is a good choice).
Click to expand...

<g> Not to mention the ONLY choice...

(Well, you can also use GUID autonumbers which require a GUID foreign
key).

John W. Vinson[MVP]
 
Back
Top