I'm getting the hang of database architecture design I think, along
with easy to code, drag-and-drop Access 2003 forms programming--great
front end.

But I have a question about a form involving three tables--and I'm not
sure if this is a programming question or a database architecture
question, hence the crosspost.

I have three tables to model a stock portfolio (buying and selling by
a single person having numerous accounts):  Stock_Accounts (plural),
in a single table (red flag), which belong to a single individual,
then a stock table, Stocks, listing all the stocks owned by the
individual, then a stock transaction table, Stock_transactions,
listing all the buying and selling within the various accounts.  FYI
the table "Stock_transactions" is a subform (depends on a parent) of
"Stocks", while Stocks is a subform (depends on a parent) of
"Stock_Accounts", meaning there's a one-to-many relationship from form
to subform.

Everything works fine:  everything is in first normal form with
primary and foreign keys, but one nagging problem:  in the rare event
that this person owns the same stock in two different accounts, the
way I set up the tables will not allow the person to enter the same
symbol.  Quick workaround:  require a different symbol, say "IBM2"
with a popup warning box to the user explaining why.  Another
workaround (I tried this and it works): is to eliminate the stock
symbol as a primary/foreign key--that's fine, and it works, but now
the problem is that within the same Stock account you can accidentally
enter the same stock symbol twice, which is a data integrity problem.

So a third approach:  enforce relational integrity between tables for
stock symbol with keys involving a stock symbol, but break up the
different accounts into seperate tables--Account 1, Account 2, Account
IRA, etc.  Thus entering the same stock in Account 2 will be
irrelevant for this stock in Account 1, exactly as we desire.  This
might be the best approach.

A fourth approach:  somehow, within the tables, enforce that the same
field cannot be entered twice, programmically--is there a way to do
that in Access?

A fifth approach: instead of a clean "one-to-many" relationship have a
"many-to-many" relationship between the tables, so stock symbol
becomes a key but a key that is spread around (via an intermediate
junction table).

As I type this, I believe the cleanest approach is simply to have many
tables for different stock accounts for this individual:  one table
per brokerage, say the person might have an IRA stock account, a
speculative stock account, a conservative stock account, etc, with
different stock brokerage account numbers, and with the accounts all
buying on occasion the same stock (same stock symbol), and that's
fine.

Any thoughts?

RL