Microsoft Access

[Rambyte]

In Access 97 you are restricted to 255 Attribute Names in a data base. Is
that restriction in the 2007 version?

 

[Allen Browne]

Yes. A2007 still handles 255 fields in a table.

 

[Rambyte]

Is there a way to create a data base with more than 255 Attribute names? Like
using sub data bases or something.

 

[Allen Browne]

It might be helpful if you told us what you needed to to.

A relational database is designed around a dozen or so very specific rules
that indicate what data to keep in which tables, and how to relate them. 255
fields is more than double what you are ever likely to need.

If you don't understand how to normalize data, Jeff Conrad has a bunch of
links here to get you started:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

 

[Jeff Boyce]

I'm with Allen on this. What are you trying to accomplish?

While it would be likely to use up to hundreds of columns for an Excel
spreadsheet, a relational database (e.g., Access) works best when you do NOT
feed it 'sheet data.

What kind of data are you planning to store in your "more than 255" fields?
I ask, not to be snoopy, but to see if there might be an alternative
approach that will help you make better use of Access' features/functions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

Is there a way to create a data base with more than 255 Attribute names? Like
using sub data bases or something.

I've needed as many as 60 fields in a table... once. Special case.

255 fields is ENORMOUSLY more than any properly normalized table
should ever require. "Fields are expensive, records are cheap". You
may be falling into the "spreadsheet trap", storing a one to many
relationship in a single record rather than splitting it out into a
separate table!

A common reason to hit this trap is a questionnaire; it's tempting to
use one field per question. However it is not necessary to do so. For
a normalized solution, see Duane Hookum's _At Your Survey_ sample
database:

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'

If you wish, feel free to describe the nature of your data and why you
feel that you need so many fields.

John W. Vinson [MVP]

 

[Rambyte]

I am working in Geo_media Pro. I am trying to set up a data base for the
fibre on a base. There are 157 fibres on the base and each fibre has to have
8 pieces of information about it, or 8 attribute headings for one fibre times
157 fibres = 1256 attribute headings inserted in sets of 8 each set being
numbered 1 - 157.
Thanks for helping.

 

[Jason Lepack]

Depending on how stable these 8 attributes are you have two options.

If your attribute NEVER change then you could have a table with ten
colums:
8 attribute values
1 fibre number
1 date of record

Then the most recent date for a given fibre number is the current
value.

Now if your attributes are changing (I don't know what the GeoMedia
Pro is) then you would create an fibre_attributes table the would hold
the description of the attribute and an attribute_id then your main
table would contain records with:
1 fibre_number
1 fibre_attribute_id
1 attribute value
1 date of record

Then the most recent record for each fibre and fibre attribute is the
current value.

Cheers,
Jason Lepack

 

[John W. Vinson]

I am working in Geo_media Pro. I am trying to set up a data base for the
fibre on a base. There are 157 fibres on the base and each fibre has to have
8 pieces of information about it, or 8 attribute headings for one fibre times
157 fibres = 1256 attribute headings inserted in sets of 8 each set being
numbered 1 - 157.
Thanks for helping.

Think vertically, not horizontally.

If you have one Base with multiple Fibres, then you have a *ONE TO MANY*
relationship. You would have a table of bases (with eight records, at least at
present); this would be related to a table with two (or maybe a few more)
fields, one for the BaseID (a link to the primary key of the Bases table), and
one for the fiber. Each base will have 157 records in this table.

If the eight attributes are static and not repeating, just have eight more
fields in this table, one for each attribute. If the attributes may change,
you have *another* one to many relationship, and need another table - with
fields for the FiberID, the name of the attribute, and the value of the
attribute.

John W. Vinson [MVP]