Foreign keys

[ckloch]

how do you establish a foreign key.

Fund detail table
Fund ID
EffectiveDate
ID
tblFundDetail_ID
NewFund
NewTicker
New Category

fund data table
Fund ID
Fund Name
Ticker
Category Name

I want a relationship between the the fund id in both tables and a
relationship between the new fund and the fund name...

Also is there any way I can get the new ticker and new category to
automatically update?

 

[Tom Wickerath]

There is nothing special that you do to the field itself, unlike the primary
key, where you have to select which field(s) to specify as the primary key.
The foreign key is simply a primary key from another table (ie. the same data
type). In the case of an autonumber PK, the matching data type is a number /
long integer.

A foreign key is established as soon as you create a relationship between
two fields, using Tools > Relationships. Note: You should enforce referential
integrity (RI) when you are creating the relationship.

Defining relationships between tables
http://support.microsoft.com/?id=304467


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Jerry Whittle]

Also is there any way I can get the new ticker and new category to

automatically update?

You should not. If you have that data in the fund data table, you shouldn't
repeat it in the Fund detail table. Which table is the parent and which the
child?

For example let's say that I have two tables: Customers and Orders.

The Customer table would have fields like:
CustID CustName CustStreetAddress CustCity etc.

The Orders table would have:
OrderID CustID OrderDate etc.

The Customer.CustID field is the primary key for that table and linked to
the Orders.CustID which is the foriegn key. That way I don't have to retype
the CustName, CustStreetAddress, etc., for each Order. I just join them by a
query when I need to see the Customer information with the Order information.

You need to decide what information goes in what table and set up
relationships.