Field Limit

[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?

 

[Roger Carlson]

You should not have repeated columns (Building1, Building2...etc). You
should simply have one instance of each and store the multiples in
additional ROWS.

This is fundamental normalization, and you should do some research on it
before you go any further.

I've got a couple of blog series that introduce the topic.
I suggest you start here:

What is Normalization?
http://rogersaccessblog.blogspot.com/2008/12/what-is-normalization-part-i.html

Entity-Relationship Diagramming
http://rogersaccessblog.blogspot.com/2009/01/entity-relationship-diagramming-part-i.html

and follow these up with some more detailed examples here:
Database Design Tutorials
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=238

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Al Campagna]

Cole Davidson,
Your missing the concept of a relational database.

You should have a table of BuildingOwners with a unique OwnerID, and a
separate table for all Buildings... also with a OwnerID field.
The Owners table (the ONE), related to the Buildings table (the MANY) by
a key field OwnerID.
This is a basic One to Many relationship.

Since your Buildings are in an associated table... an owner could have
60 buildings, 100 buildings, ... etc
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Jerry Whittle]

And your house of cards will tumble done the first time that you have someone
with 51 or more buildings.

You need a Policy Holder table with all the information about the policy
holder.

You need a Building table for info on the building like it's address and
value.

You need a Policy amount table with info on the Policy such as insured
amount. Note that you don't need a Difference field in any of the tables. You
compute the difference between the Estimated Value and Insured Amount as
needed.

Then you join these tables together with relationships in queries, forms,
and reports. Advantages include not having a 255 field problem; not having to
worry about how many buildings a person owns; makes it easier to deal with
when a person sells or buys another building (especially if it's to an
existing policy holder).

I highly recommend getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez before proceeding any further
on this database.

 

[Cole Davidson]

Thanks for your advice Jerry, but I dont need all this data.

I simply need Policy Holder name, building name, building type, insured
value, estimated value, difference, and % difference.

I dont need address and such.

And your house of cards will tumble done the first time that you have someone
with 51 or more buildings.

You need a Policy Holder table with all the information about the policy
holder.

You need a Building table for info on the building like it's address and
value.

You need a Policy amount table with info on the Policy such as insured
amount. Note that you don't need a Difference field in any of the tables. You
compute the difference between the Estimated Value and Insured Amount as
needed.

Then you join these tables together with relationships in queries, forms,
and reports. Advantages include not having a 255 field problem; not having to
worry about how many buildings a person owns; makes it easier to deal with
when a person sells or buys another building (especially if it's to an
existing policy holder).

I highly recommend getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez before proceeding any further
on this database.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

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?

 

[aaron.kempf]

CORRECTION:
you _SHOULD_ use repeated rows sometimes, where appropriate. If your
database doesn't fit your needs-- don't blame it on theory-- grow into
a database that is used by real professionals.

ANSWER:
Jet has a hard limit of 255 columns. Even MS Project gets awfully
close to breaking this limit.. so when people tell you that you're not
normalized-- just because you have 200+ columns-- don't listen to
them.

SQL Server has no such limit.
As a matter of fact-- they added support for SPARSE columns which
makes really wide tables perform better (when you have some empty
fields)
http://www.kodyaz.com/articles/sql-server-2008-sparse-columns.aspx

You should not have repeated columns (Building1, Building2...etc).  You
should simply have one instance of each and store the multiples in
additional ROWS.

This is fundamental normalization, and you should do some research on it
before you go any further.

I've got a couple of blog series that introduce the topic.
I suggest you start here:

What is Normalization?http://rogersaccessblog.blogspot.com/2008/12/what-is-normalization-pa...

Entity-Relationship Diagramminghttp://rogersaccessblog.blogspot.com/2009/01/entity-relationship-diag...

and follow these up with some more detailed examples here:
Database Design Tutorialshttp://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=238

--
--Roger Carlson
  MS Access MVP
  Access Database Samples:www.rogersaccesslibrary.com
  Want answers to your Access questions in your Email?
  Free subscription:
 http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

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 or3,
while others have up to 50. So the way I have it set up, the table needto
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?- Hide quoted text -

- Show quoted text -

 

[John W. Vinson]

Thanks for your advice Jerry, but I dont need all this data.

I simply need Policy Holder name, building name, building type, insured
value, estimated value, difference, and % difference.

Reread Jerry's suggestion. This has NOTHING WHATSOEVER to do with what other
fields you need. It has to do with using Access as it was designed, for its
intended purpose, rather than treating it as if it were Excel on steroids.

A table datasheet may look like a spreadsheet. IT ISN'T ONE. It doesn't work
like one at all well; if you coerce it to do so (as you are doing) you will
have no end of trouble. If you use Access as what it is - a relational
database, with a table for PolicyHolders, a table of Buildings, and queries to
calculate the difference and percent difference - you'll find that Access is
very simple to use. If you persist in using fifty building fields in your
table, you'll find that Access is a major pain and inconvenience. It's just a
matter of using the tool correctly!

 

[BruceM]

One client has one building, while another has 50, so I don't think this is
one of those appropriate times. Even if the OP allows for 50, what if the
business expands and they have a customer with 60 buildings? There is no
way to know how many extra fields is enough in such a case. Design
principles continue to apply no matter the engine.

CORRECTION:
you _SHOULD_ use repeated rows sometimes, where appropriate. If your
database doesn't fit your needs-- don't blame it on theory-- grow into
a database that is used by real professionals.

ANSWER:
Jet has a hard limit of 255 columns. Even MS Project gets awfully
close to breaking this limit.. so when people tell you that you're not
normalized-- just because you have 200+ columns-- don't listen to
them.

SQL Server has no such limit.
As a matter of fact-- they added support for SPARSE columns which
makes really wide tables perform better (when you have some empty
fields)
http://www.kodyaz.com/articles/sql-server-2008-sparse-columns.aspx

You should not have repeated columns (Building1, Building2...etc). You
should simply have one instance of each and store the multiples in
additional ROWS.

This is fundamental normalization, and you should do some research on it
before you go any further.

I've got a couple of blog series that introduce the topic.
I suggest you start here:

What is
Normalization?http://rogersaccessblog.blogspot.com/2008/12/what-is-normalization-pa...

Entity-Relationship
Diagramminghttp://rogersaccessblog.blogspot.com/2009/01/entity-relationship-diag...

and follow these up with some more detailed examples here:
Database Design
Tutorialshttp://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=238

--
--Roger Carlson
MS Access MVP
Access Database Samples:www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

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?- Hide quoted text -

- Show quoted text -

 

[Roger Carlson]

I didn't say anything about not using repeated rows, I said you shouldn't
use repeated COLUMNS.

Repeated columns violates the definition of First Normal Form, the very
basic arrangement of fields in a table. Jerry's post illustrates just of
the problems with doing so: How many columns do you define? And what happens
when you have a situation where you need one more?

There ARE times when denormalizing your tables is advantageous, principly
for performance reasons or in a Data Warehouse. But this case is not one of
them. Database theory is important because it WORKS, as any real
professional knows.

--
--Roger Carlson
MS Access MVP
www.rogersaccesslibrary.com



CORRECTION:
you _SHOULD_ use repeated rows sometimes, where appropriate. If your
database doesn't fit your needs-- don't blame it on theory-- grow into
a database that is used by real professionals.

ANSWER:
Jet has a hard limit of 255 columns. Even MS Project gets awfully
close to breaking this limit.. so when people tell you that you're not
normalized-- just because you have 200+ columns-- don't listen to
them.

SQL Server has no such limit.
As a matter of fact-- they added support for SPARSE columns which
makes really wide tables perform better (when you have some empty
fields)
http://www.kodyaz.com/articles/sql-server-2008-sparse-columns.aspx

You should not have repeated columns (Building1, Building2...etc). You
should simply have one instance of each and store the multiples in
additional ROWS.

This is fundamental normalization, and you should do some research on it
before you go any further.

I've got a couple of blog series that introduce the topic.
I suggest you start here:

What is
Normalization?http://rogersaccessblog.blogspot.com/2008/12/what-is-normalization-pa...

Entity-Relationship
Diagramminghttp://rogersaccessblog.blogspot.com/2009/01/entity-relationship-diag...

and follow these up with some more detailed examples here:
Database Design
Tutorialshttp://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=238

--
--Roger Carlson
MS Access MVP
Access Database Samples:www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

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?- Hide quoted text -

- Show quoted text -

 

[BruceM]

What do you tell the potential customer with 60 buildings? The point is not
the amount of information about each building, as John observed, but rather
that each entity belongs in its own table. A policy declaration is one type
of entity; buildings are another.

There are many advantages to storing building information in a separate
table. One is that searching is much simpler, as you need to check one
field rather than 50 is you are trying to find specific information.

Do you have a separate Policy Holder table? If so, and if you store the
Policy Holder Name in the Declarations table, you will need to update
records if a Policy Holder name changes; otherwise you will have no record
from before the name change. With something like an Employee table you
would want to retain information about the employee even if their name
changes. A person's accumulated benefits don't go away if they change their
name. In the same way, I would think you want continuity for a policy
holder. In both cases it is usually best to store an unchanging ID number.

Another point I would make is that you would do not need to store the
difference and the %. These can and should be calculated on the fly as
needed. If the insured or estimated value changes there is no need to
recalculate the difference or the %. It will be there the next time you
view it in a form or report. For instance, for Difference you could have a
query field:
Difference: [InsuredValue] - [EstimatedValue]

Thanks for your advice Jerry, but I dont need all this data.

I simply need Policy Holder name, building name, building type, insured
value, estimated value, difference, and % difference.

I dont need address and such.

And your house of cards will tumble done the first time that you have
someone
with 51 or more buildings.

You need a Policy Holder table with all the information about the policy
holder.

You need a Building table for info on the building like it's address and
value.

You need a Policy amount table with info on the Policy such as insured
amount. Note that you don't need a Difference field in any of the tables.
You
compute the difference between the Estimated Value and Insured Amount as
needed.

Then you join these tables together with relationships in queries, forms,
and reports. Advantages include not having a 255 field problem; not
having to
worry about how many buildings a person owns; makes it easier to deal
with
when a person sells or buys another building (especially if it's to an
existing policy holder).

I highly recommend getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez before proceeding any
further
on this database.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

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?

 

[aaron.kempf]

there's nothing wrong with breaking any normal form.

if your database doesn't fit what you're trying to do -- you should
try to move to a database that fits your needs.

Not everything should be perfectly (overly) normalized.

and this 255 field limit was reason #1 I moved away from using Jet.

-Aaron

One client has one building, while another has 50, so I don't think this is
one of those appropriate times.  Even if the OP allows for 50, what if the
business expands and they have a customer with 60 buildings?  There is no
way to know how many extra fields is enough in such a case.  Design
principles continue to apply no matter the engine.


CORRECTION:
you _SHOULD_ use repeated rows sometimes, where appropriate.  If your
database doesn't fit your needs-- don't blame it on theory-- grow into
a database that is used by real professionals.

ANSWER:
Jet has a hard limit of 255 columns.  Even MS Project gets awfully
close to breaking this limit.. so when people tell you that you're not
normalized-- just because you have 200+ columns-- don't listen to
them.

SQL Server has no such limit.
As a matter of fact-- they added support for SPARSE columns which
makes really wide tables perform better (when you have some empty
fields)http://www.kodyaz.com/articles/sql-server-2008-sparse-columns.aspx

You should not have repeated columns (Building1, Building2...etc). You
should simply have one instance of each and store the multiples in
additional ROWS.

This is fundamental normalization, and you should do some research on it
before you go any further.

I've got a couple of blog series that introduce the topic.
I suggest you start here:

What is
Normalization?http://rogersaccessblog.blogspot.com/2008/12/what-is-normalization-pa...

and follow these up with some more detailed examples here:
Database Design
Tutorialshttp://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=238

--
--Roger Carlson
MS Access MVP
Access Database Samples:www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

messagenews:[email protected]...

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 differencein
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?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Rick Brandt]

there's nothing wrong with breaking any normal form.

if your database doesn't fit what you're trying to do -- you should try
to move to a database that fits your needs.

Not everything should be perfectly (overly) normalized.

and this 255 field limit was reason #1 I moved away from using Jet.

-Aaron

What a surprise. Your #1 reason to move away from Jet was that it
limited how poor a database designer you can be. I'm sure you were able
to achieve your full potential with SQL Server.

Hey, UDB400 allows almost 8 times as many fields in a table (8000)
compared to SQL Server. Maybe you should switch.

 

[BruceM]

Breaking normal form to have an Address1 and Address2 field is one thing.
Adding 200 or more fields to allow for anywhere from 1 to 50 buildings is
quite another. Your recommendation to use SQL Server in order to create a
giant spreadsheet is unsound advice.

there's nothing wrong with breaking any normal form.

if your database doesn't fit what you're trying to do -- you should
try to move to a database that fits your needs.

Not everything should be perfectly (overly) normalized.

and this 255 field limit was reason #1 I moved away from using Jet.

-Aaron

One client has one building, while another has 50, so I don't think this
is
one of those appropriate times. Even if the OP allows for 50, what if the
business expands and they have a customer with 60 buildings? There is no
way to know how many extra fields is enough in such a case. Design
principles continue to apply no matter the engine.


CORRECTION:
you _SHOULD_ use repeated rows sometimes, where appropriate. If your
database doesn't fit your needs-- don't blame it on theory-- grow into
a database that is used by real professionals.

ANSWER:
Jet has a hard limit of 255 columns. Even MS Project gets awfully
close to breaking this limit.. so when people tell you that you're not
normalized-- just because you have 200+ columns-- don't listen to
them.

SQL Server has no such limit.
As a matter of fact-- they added support for SPARSE columns which
makes really wide tables perform better (when you have some empty
fields)http://www.kodyaz.com/articles/sql-server-2008-sparse-columns.aspx

You should not have repeated columns (Building1, Building2...etc). You
should simply have one instance of each and store the multiples in
additional ROWS.

This is fundamental normalization, and you should do some research on it
before you go any further.

I've got a couple of blog series that introduce the topic.
I suggest you start here:

What is
Normalization?http://rogersaccessblog.blogspot.com/2008/12/what-is-normalization-pa...

and follow these up with some more detailed examples here:
Database Design
Tutorialshttp://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=238

--
--Roger Carlson
MS Access MVP
Access Database Samples:www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

messagenews:[email protected]...

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?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -