Relationships

C

Christene

When you create a relationship does the relation have to have the same name
EX. Customer ID---Customer ID and when you create a relationship does it
relate the whole table or just the one field....Please help
 
F

freakazeud

Hi,
a relationship is defined through a primary and foreign key. Both should
have the same name. The primary key should be an autonumber and the foreign
key of number data type. You then have to open the relationship window and
draw the relationship between the two.
The relationship relates the data in both tables. E.g. if you have a table
with FirstName and LastName and then a table with a field Addresses you can
relate address records from the one table with name records in the other.
HTH
Good luck
 
C

Christene

So If I have a table with all the customer information I would set the
CustomerID as the primary field as an Auto Number, Then in my Loans Invested
in table I would set the CustomerID field as a Number field with a key. So if
I have a third table that has what the loans are and how much ext. what would
I do there? And I thought you were suppose to have an Auto Number in each
table?
 
F

freakazeud

Yes each table needs a primary key which should be of type autonumber. Here
is the setup as far as I can see from the information given:

CustomerTable
CustomerID (PrimaryKey/Autonumber)
FirstName
LastName
Comments
....

LoansTable
LoansID (PrimaryKey/Autonumber)
LoansName
LoansDescription
....

LoansInvestedTable
LoansInvestedID (PrimaryKey/Autonumber)
CustomerID (ForeignKey/Number)
LoansID (ForeignKey/Number)
InvestedWhen
Comments
....

HTH
Good luck
 
C

Christene

how do i create a foreign key

freakazeud said:
Yes each table needs a primary key which should be of type autonumber. Here
is the setup as far as I can see from the information given:

CustomerTable
CustomerID (PrimaryKey/Autonumber)
FirstName
LastName
Comments
...

LoansTable
LoansID (PrimaryKey/Autonumber)
LoansName
LoansDescription
...

LoansInvestedTable
LoansInvestedID (PrimaryKey/Autonumber)
CustomerID (ForeignKey/Number)
LoansID (ForeignKey/Number)
InvestedWhen
Comments
...

HTH
Good luck
 
C

Christene

It worked Thank you so much. Do I need to relate the LoansInvestedID to
anything or just leave it alone?
 
D

Douglas J. Steele

<picky>
It's not mandatory that the primary and foreign key be named the same. I've
worked on databases where, say, the Customer table had a primary key of Id,
but the foreign keys were named CustomerId.

Also, I don't agree that the primary key "should" be an autonumber, but
that's a religious argument. <g> I'm certainly not going to introduce an
autonumber in a table of state or province names: I'd use the 2 letter
abbreviation.
</picky>
 
S

Steve Schapel

Christene,

You don't need a LoansInvestedID field at all. If you will have another
table in your database that is on the "many" side of a one-to-many
relationship with the LoansInvested table, it would be convenient to put
a LoansInvestedID autonumber field in there, but from what you have
described I imagine this is a very unlikely scenario. Personally, I
would not put a LoansInvestedID field in that table.

And, as also indicated by Doug Steele, the answer to your question
elsewhere about an Autonumber field in every table, the answer is
emphatically No, there are many examples of tables where this does not
apply.
 
Top