specific sub-categories

[mack7963]

Hi Guys
So, i have a problem, i need to build a form that will display specific info about a company, example i am working with a lot of of insurance companies and i need to display what is their main business and what areas they cover , example:

1 Liverpool Victoria Insurance House, Contents, pet, life
2 SunLife Insurance Health, Life, Retirement
3 YachtSafe Insurance Fleet, Single, Docking, Journey
4 2Wheel Insurance MotorBike,
5 The AA Insurance Car, Van, Breakdown

what i can't figure out is this, how do i get it so that only the insurance types are related to the insurance company that deals with them, i know the answer is jumping up and down staring me defiantly in the face with its tounge sticking out, but i have racked my brains and i just cant see it, is it a 1 to many, a many to one or a many to many i need to use. hope someone can help.
Mack

[CJ_London]

you have what is called a many to many relationship - one insurance co can have many policy types, one policy type can have many insurance companies.

To handle many to many relationships you need three tables - one for the insurance companies, one for the policy types and a third to link them together.

tblInsuranceCompanies
CompanyPK autonumber
CompanyName text
....
...

tblPolicyTypes
PolicyPK autonumber
PolicyType text

tblCompanyPolicyType
CPTPK autonumber
CompanyFK long
PolicyTypeFK long

PK = Primary Key
FK = Foreign (or Family) Key



The data might look like this

tblInsuranceCompanies
CompanyPK...CompanyName
1..................LiverpoolVictoria
2..................SunLife
tblPolicyTypes
PolicyPK...PolicyType
1.............Contents
2.............Pets
3.............Life
tblCompanyPolicyType
CPTPK...CompanyFK...PolicyTypeFK
1..........1..................2
2..........1..................3
3..........1..................4
4..........2..................2
5..........2..................1
6..........2..................3

[mack7963]

Ajax thank you so much, your explanation definitely helped, i actually got the form to work how i was hoping it would, ended up using two junction tables, one for the relationship between the Company Name and the Subesctor and another for the relastionship between the Sector and the subsector, now when i bring up an insurance company name it tells me what types of insurance they cover, same with banks, so thank you.