One to many table

[kazprog]

Can anybody help with a table design problem I am trying to get my head around. My employer wants me to store market types. Each market type has any number of sub types attatched to it. I have set up two tables as a one to many relationship. One that has

Market type number and description

the other has

Market type number,
Market type sub number
and description

How can I get the form to just select the sub types that it belongs to instead of selecting the whole table when you click on the field in the table.

I need it to just select the sub types that relate to the main market types.
Hope this makes sense. Can anybody help?
Kink regards
Kaz

 

[THWatson]

Kaz
Can you set up the Main form with the Market type number as its record source (the one side in the relationship) and then bring in the other table (the many side) as a subform, linking them on the market type number?

That should keep the main form and its sub types properly related.

Tom

 

[JeremyNYC]

This is a many to many relationship, and can be modeled best with three tables:

tblMarket:
MarketID (autonum PK)
whatever other fields

tblSubType
SubtypID (autonum PK)
whatever other fields

tblMarketSubtype
MarketID (long int)
SubtypeID (long int)
(PK is the two fields together)

This allows you to have one market with a bunch of different sub types and one subtype that is present in a bunch of different markets.

It's a very common thing, and one you'll soon become very familiar with.

There's an article on my website by Paul Litwin that discusses the reasons for doing this. It's a great article. It's in the Developers' section, and it's called "Fundamentals of Relational Database Design".

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.