Multiple Entries
in [Tables]
|
From: MPAVLAS on 21 Aug 2008 12:12 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?
From: Klatuu on 21 Aug 2008 12:47 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?
From: Steve on 21 Aug 2008 13:21 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" <MPAVLAS(a)discussions.microsoft.com> wrote in message news:46BE3BB0-AD2E-4137-9051-7D678A4B8992(a)microsoft.com... >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?
From: Flavelle Ballem on 26 Aug 2008 15:18 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?
|
Pages: 1 Prev: Attaching pictures in Access Next: GetOleDbSchemaTable(OleDbSchemaGuid.Columns,... |