Need to restructure.


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

Larry Daugherty

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

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.


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question