Multiple Entries

I have a datbase that contains the names of insurance agents in my area. I
want to add a field that shows what insurance companies they provide for. I
know how to set it up when they each handle one insurance but how can I set
it up when they handle more than one (up to 10 or more). My ultimate goal is
to be able to pull agent names by insurance company.

Do I have to create 10 fields, or is there a different way?

You need two additional tables and if your agents table doesn't have an
autonumber or some other primary key, you need one.

so the basic concept is:
tblAgent
AgentID - Primary key

tblInsCompany
CompID - Primary Key

tblCompanyAgent
CompID - Foreign Key to tblInsCompany
AgentID - Foreign Key to tblAgent

The third table is a junction table to resolve a many to many relationship.
Now you can tell every company an agent represents as well as what agents
represent a company.
--
Dave Hargis, Microsoft Access MVP


"MPAVLAS" wrote:

I have a datbase that contains the names of insurance agents in my area. I
want to add a field that shows what insurance companies they provide for. I
know how to set it up when they each handle one insurance but how can I set
it up when they handle more than one (up to 10 or more). My ultimate goal is
to be able to pull agent names by insurance company.

Do I have to create 10 fields, or is there a different way?

You need a set of properly designed tables ............

TblInsuranceCompany
InsuranceCompanyID
InsuranceCompany
other co,pany fields you need

TblInsuranceAgency
InsuranceAgencyID
InsuranceAgency
other agency fields you need

TblInsuranceAgencyCompany
InsuranceAgencyCompanyID
InsuranceAgencyID
InsuranceCompanyID

TblInsuraneAgent
InsuraneAgentID
FirstName
LastName
Insurasnce agent contact fields
InsuranceAgencyID


Steve




"MPAVLAS" wrote in message
...
I have a datbase that contains the names of insurance agents in my area. I
want to add a field that shows what insurance companies they provide for.
I
know how to set it up when they each handle one insurance but how can I
set
it up when they handle more than one (up to 10 or more). My ultimate goal
is
to be able to pull agent names by insurance company.

Do I have to create 10 fields, or is there a different way?

A couple of questions around the requirements:

1/ Can an insurance agent work for more than one Insurance Agency at a point
in time? You may also need to allow for the possibility that if the anser is
'no', then they may change agencies. If they change agencies, do you need to
keep a historical record?

2/ Is the important relationship between the Insurance Agency and the
Insurance Companies, or between the Insurance Agent and the Insurance
Companies? The question is intended to determine what you need to keep track
of.

There may be other questions, depending on the answers to these, but there
are multiple ways to design the tables.

For example, if the answer to 1 is 'no - an agent can only work for one
agency' and the answer to 2 is 'important relationship is between agency and
company', then that would suggest:

tblAgent, containing the information about a specific Agent.
tblAgency, containing the information about a specific Agency.
tblCompany, containing the information about a specific Insurance Company.
tblAgencyAgent, which would link entries from tblAgent to tblAgency.
tblCompanyAgency, which would link entries from tblAgency to tblCompany.

You could then determine if a specific agent could represent a specific
company indirectly.

On the other hand, if the important link is between the Agent and the
Company, then:

tblAgent, containing the information about a specific Agent.
tblAgency, containing the information about a specific Agency.
tblCompany, containing the information about a specific Insurance Company.
tblAgencyAgent, which would link entries from tblAgent to tblAgency.
tblCompanyAgent, which would link entries from tblAgent to tblCompany. This
is the table that is different from the previous solution.

If you try to do both directly, then you may have a maintenance nightmare,
since an agent may change agencies and is no longer able to sell or maintain
policies from a Company that they previously could.

Hope this helps to identify the specific questions that need to be answered
in order to determine the correct solution to your problem.


"MPAVLAS" wrote:

I have a datbase that contains the names of insurance agents in my area. I
want to add a field that shows what insurance companies they provide for. I
know how to set it up when they each handle one insurance but how can I set
it up when they handle more than one (up to 10 or more). My ultimate goal is
to be able to pull agent names by insurance company.

Do I have to create 10 fields, or is there a different way?