Normalized Tables

[TeeSee]

Some time ago I posted a question about tabbed forms and it was
pointed out that I should rethink my table construction. John Vinson
suggested the following .......
I have constructed these three tables as suggested. ContractorID
fields have a one to many relationship as well as the TradeID fields.
Those are the only two "lines" shown in the relationships window. Ref
Int enforced in each instance.
If I test this as a Contractors MainForm with Trades and
ContractorTrades as subforms ...... Both subforms link to the
ContractorID field but I can't figure out how the TradeID field in the
ContractorTrades table gets its data.

Is it correct to have referantial integrity enforced in the linking
table?

Contractors
ContractorID <primary key>
<name, contact information, identifying information for the
contractor>
<NOTHING about the trades that they follow!!>
Trades
TradeID <primary key>
Description
ContractorTrades
ContractorID <field 1 of two-field primary key>
TradeID <field 2 of two-field primary key>
<maybe other fields pertaining to THIS contractor in THIS trade,
e.g. yes/no
field Licensed, date/time field DateLicensed, Comments, etc.>

For the record I was way off base in understanding the importance of
proper table construction and still somewhat confused .... but we'll
get there.

Thanks as usual for any assistance.

 

[Tom van Stiphout]

On Tue, 7 Apr 2009 01:13:30 -0700 (PDT), TeeSee

This is a classic many-to-many (M:M) relation. Your tables and
relations are correct. I am not so sure about the form(s). The
standard way to express a M:M in forms is to have a parent form, say
Contractors, and then a subform on the junction table
(ContractorTraders). It will have a hidden ContractorID field (filled
by the LinkChildFields property of the subform control), and a
dropdown list of trades (2-column dropdown, hidden TradeID fills
ContractorTraders.TradeID).

-Tom.
Microsoft Access MVP