Need to restructure.

  • Thread starter Thread starter Cole Davidson
  • Start date Start date
C

Cole Davidson

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?
 
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.
 
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
 
Back
Top