If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Need to restructure.
I have to set up a database for an insurance company to store declaration
page information. I need the following fields: PolicyHolder (name of the policy holder), BuildingName1 (name of the first building), BuildingType1 (type of the first building, I use lookup with a seperate table for this), InsuredValue1 (the amount the first building is insured for), EstimatedValue1 (the amount the first building is worth), Difference1 (the difference in dollars between InsuredValue1 and EstimatedValue1, I use "Control Source: [InsuredValue1] - [EstimatedValue1]", and Difference%1 (the difference in %, I use "Control Source: [Difference1] / [InsuredValue1] * 100"). This all works out for me, however I have a few hundred policy holders to add to the database, and each policy holder has multiple buildings. Some have 2 or 3, while others have up to 50. So the way I have it set up, the table need to have 6 fields X 50 buildings, which equals 300 fields. Access has a limit of 255 fields. I know I am doing this the hard way, but I dont know how else to structure the database. Any suggestions? |
#2
|
|||
|
|||
Need to restructure.
You do not need that many fields. Just these --
1- BldgID 2- PolicyHolder 3- BuildingName 4- BuildingType 5- InsuredValue 6- EstimatedValue You might add some date fields for when insured and when dropped. Use a new record for each building. Calculate those other fields when needed in a query for display in a form or report. -- KARL DEWEY Build a little - Test a little "Cole Davidson" wrote: I have to set up a database for an insurance company to store declaration page information. I need the following fields: PolicyHolder (name of the policy holder), BuildingName1 (name of the first building), BuildingType1 (type of the first building, I use lookup with a seperate table for this), InsuredValue1 (the amount the first building is insured for), EstimatedValue1 (the amount the first building is worth), Difference1 (the difference in dollars between InsuredValue1 and EstimatedValue1, I use "Control Source: [InsuredValue1] - [EstimatedValue1]", and Difference%1 (the difference in %, I use "Control Source: [Difference1] / [InsuredValue1] * 100"). This all works out for me, however I have a few hundred policy holders to add to the database, and each policy holder has multiple buildings. Some have 2 or 3, while others have up to 50. So the way I have it set up, the table need to have 6 fields X 50 buildings, which equals 300 fields. Access has a limit of 255 fields. I know I am doing this the hard way, but I dont know how else to structure the database. Any suggestions? |
#3
|
|||
|
|||
Need to restructure.
Any time you find yourself trying to deal with more than one instance
(or a variable number of instances) of an attribute (Building, in this case) you should create a separate, related, table for that attribute as an entity. In this case, each record in tblBuiulding would have a copy of the value of the Primary Key of tblPolicyHolder as a Foreign Key. Open the Relationships window and draw a link between tblPolicyHolder.[PrimaryKey] to tblBuilding.[ForeignKey] (for clarity, the field names should be the same in both tables). Double click on the link and establish Referential Integrity and turn on cascading delete. HTH -- -Larry- -- "Cole Davidson" wrote in message ... I have to set up a database for an insurance company to store declaration page information. I need the following fields: PolicyHolder (name of the policy holder), BuildingName1 (name of the first building), BuildingType1 (type of the first building, I use lookup with a seperate table for this), InsuredValue1 (the amount the first building is insured for), EstimatedValue1 (the amount the first building is worth), Difference1 (the difference in dollars between InsuredValue1 and EstimatedValue1, I use "Control Source: [InsuredValue1] - [EstimatedValue1]", and Difference%1 (the difference in %, I use "Control Source: [Difference1] / [InsuredValue1] * 100"). This all works out for me, however I have a few hundred policy holders to add to the database, and each policy holder has multiple buildings. Some have 2 or 3, while others have up to 50. So the way I have it set up, the table need to have 6 fields X 50 buildings, which equals 300 fields. Access has a limit of 255 fields. I know I am doing this the hard way, but I dont know how else to structure the database. Any suggestions? |
Thread Tools | |
Display Modes | |
|
|