Many to Many Relationships orJunction Table

[potter]

Alright...How does the Juction table get updated?

example: (I hope it's clear)
tbl_Sec changes almost daily. I import and append data to the table almost
every day. tbl_Accts changes less often. I import and append only once or
twice a month. Considering I am adding new data there could be new
relationships between tbl_SEC and tbl_Acct after each import and append.

How do I make sure the Junction table keeps up with every import and Append?
I import data to either tbl_Sec or tbl_Acct but there is never manual data
entry.

 

[Duane Hookom]

A junction table suggests a third table that relates 2 or more other tables.
Could you share your table structures (significant fields only) and how they
are related?

 

[John Vinson]

Alright...How does the Juction table get updated?

example: (I hope it's clear)
tbl_Sec changes almost daily. I import and append data to the table almost
every day. tbl_Accts changes less often. I import and append only once or
twice a month. Considering I am adding new data there could be new
relationships between tbl_SEC and tbl_Acct after each import and append.

How do I make sure the Junction table keeps up with every import and Append?
I import data to either tbl_Sec or tbl_Acct but there is never manual data
entry.

Since none of us have any trace of an idea what tbl_Sec or tbl_Acct
are, or what the nature of the data might be, or what changes you're
making to them, it's more than a bit difficult to tell.

The purpose of the junction table is NOT to "keep up with every import
and append", though. If each Account is related to several Sec
entries, and each Sec entry may be related to multiple Accts, then the
information *ABOUT THAT RELATIONSHIP* is stored in the junction table.
If you add a new Acct, you need to add - from somewhere! - which Sec
values are associated with it. There's no way to automagically have
Access guess which those might be.

Are these (I'm guessing) brokerage accounts and securities in those
accounts? If so, tell me: if I were to open a new brokerage account,
what securities are in it? Or, if XYZ Corp becomes available through
an IPO, which of your accounts currently own it?


John W. Vinson[MVP]

 

[potter]

Sure....

tbl_Sec (TABLE1)
ID_CUSIP (Primary Key)
Name
Ticker
Cpn
Maturity...etc...etc...

tbl_Acct (TABLE2)
ID_Acct (Primary Key using autonumber as datatype)
Name
Acct
Date
Acct...address..Phone...etc

tbl_acct_Jnctn (TABLE3)
ID_Acct (Primary Key combined)
ID_CUSIP (Primary Key combined)
CUSIP
Name
Acct...etc.etc

This help?

 

[John Vinson]

Sure....

tbl_Sec (TABLE1)
ID_CUSIP (Primary Key)
Name
Ticker
Cpn
Maturity...etc...etc...

tbl_Acct (TABLE2)
ID_Acct (Primary Key using autonumber as datatype)
Name
Acct
Date
Acct...address..Phone...etc

tbl_acct_Jnctn (TABLE3)
ID_Acct (Primary Key combined)
ID_CUSIP (Primary Key combined)
CUSIP
Name
Acct...etc.etc

This help?

Your acct_Jnctn table should have the ID_Acct and ID_CUSIP fields as
links to the "one" side tables... *AND NOTHING ELSE*.

Storing the account or CUSIP data redudnantly in the junction table is
simply incorrect design. You're using a relational database; use it
relationally! Store the data ONCE and then use queries to join to it.

John W. Vinson[MVP]