Automatic filling of fields in table two from table one

Table one contains fields with information on stocks. Example, field one is
StockSymbol, field two is StockName, field three is Exchange, etc. Overall
there are about 30 fields per record in Table one.

Table two contains portfolios for clients. Field one of Table two has
ClientID, fields two and three are identically named to Table one's fields
one and two. These clients own various stocks (all of which are in Table One)
but many clients in Table two do not own the same stocks as other clients do.
I only want to reflect pertinent field info for clients holding stocks
contained in Table one.

I want to know if there is a way to link field information from Table one to
Table two so that as I add new clients' portfolios to Table two I can just
enter StockSymbol in field two of Table two and the StockName, etc. fields in
Table two will automatically populate with appropriate data from the
same-named fields in Table one for the stock record matching the StockSymbol
I have entered?

Since the StockSymbol is unique for each stock record in Table one, using
the StockSymbol field name in Table two should trigger a relationship between
the two tables that would allow for automatic filling-in of same-named fields
in Table two based upon identical content being entered in Table two,
StockSymbol field.

I am new to ACCESS so please be as clear as possible in your answer.

On Mon, 27 Sep 2004 10:39:02 -0700, Jim Kelly Jim
wrote:

Table one contains fields with information on stocks. Example, field one is
StockSymbol, field two is StockName, field three is Exchange, etc. Overall
there are about 30 fields per record in Table one.

Table two contains portfolios for clients. Field one of Table two has
ClientID, fields two and three are identically named to Table one's fields
one and two.

TableTwo should have ONLY the StockSymbol field. Storing the stock
name and exchange redundantly in TableTwo wastes space and (worse)
risks data validity errors, since you could have two different
StockNames for the same stock.

Rather than storing these fields in the second table, store them ONLY
in TableOne; if you need to see them in conjunction with client
portfolio data, create a Query joining the two tables on StockSymbol.
Pull the StockName and Exchange from TableOne, and the other
information from TableTwo.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps