maximum fields per table?

[eric]

Hi,

Is there a maximum number of fields allowed in each table?

(I ask because as I have had to split tables in two a
number of times..)

If so, is it possible to increase this maximum somehow?

Eric

 

[Bruce M. Thompson]

Is there a maximum number of fields allowed in each table?

(I ask because as I have had to split tables in two a
number of times..)

If so, is it possible to increase this maximum somehow?

The maximum number of fields allowed in a table is 255, and that cannot be
increased. If you need to exceed that limit, then you probably need to either
modify your data structure or (if absolutely necessary) split your tables as you
have already done. For help with your database design, see one or more of the
following:

Chapter 4: Database Design Principles (MSDN)
http://msdn.microsoft.com/library/en-us/dndbdes/html/ch04DDP.asp

Designing Relational Database Systems
by Rebecca M. Riordan (MS Press)
http://www.amazon.com/exec/obidos/A...45668/sr=11-1/ref=sr_11_1/002-5875637-5464841

Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design
by Michael J. Hernandez (Addison-Wesley)
http://www.amazon.com/exec/obidos/t..._books_1/002-5875637-5464841?v=glance&s=books

I, personally, have never encountered the need for anywhere near that number of
fields in a table, but I know that this situation does happen.

 

[John Vinson]

Hi,

Is there a maximum number of fields allowed in each table?

Yes, 255 fields.

(I ask because as I have had to split tables in two a
number of times..)

If so, is it possible to increase this maximum somehow?

No.

Note that a 255 field table is CERTAINLY not properly normalized. I've
needed as many as 60 fields, twice, in the past twenty years of
database work. (I've *used* more but didn't *need* them, and regretted
it!)

There's a saying, I don't remember whose, that bears consideration:
"Fields are expensive, records are cheap". If you design your tables
so that they grow vertically rather than horizontally you'll find
Access is much more accommodating!

What are these tables? Why do you need them so wide?

 

[TC]

Hi,

Is there a maximum number of fields allowed in each table?
255.


(I ask because as I have had to split tables in two a
number of times..)

You need to read:
http://support.microsoft.com/support/kb/articles/Q100139.ASP

If so, is it possible to increase this maximum somehow?

If you could increase it whenever you wanted to, how would it be "a
maximum"?

HTH,
TC

 

[Steve Schapel]

Eric,

If you would care to post back with some examples of the fields in
these tables, someone will be able to advise on possible improvements
to your database design.

- Steve Schapel, Microsoft Access MVP

 

[Susan]

I often read through the messages to learn -- and found
that this is one of the subjects I am reading up on. My
situation is a payroll table that I want to keep
historically with a date, hrs, payrate (to run reports on
request for any time period). My first thought was to
make a table with 52 fields (one for each week of the
year) and at the end of the year save that data to a zip
disk and rename the table on Access for the current year.
Is there a better way to do this? Thanks - Susan

 

[LeAnne]

I often read through the messages to learn -- and found
that this is one of the subjects I am reading up on. My
situation is a payroll table that I want to keep
historically with a date, hrs, payrate (to run reports on
request for any time period). My first thought was to
make a table with 52 fields (one for each week of the
year) and at the end of the year save that data to a zip
disk and rename the table on Access for the current year.
Is there a better way to do this? Thanks - Susan

<wince> Yes.

You are making the common error of organizing your data tables based on
your desired end product...a report with information grouped by each
week of a given year. Access is a _relational_ database. Use it
relationally, instead of "committing spreadsheet"! Having separate
fields for every week, and a separate table for every year <shudder>, is
not what is known as a Normalized design. In order to take advantage of
the relational capabilities of Access, your data tables should be
organized so that each field represents a unique type of information.
Repeating fields (Week1, Week2, ...,WeekX) waste space, since tables can
only expand horizontally so far, and also wastes disc storage space by
storing information that can easily be calculated in a query, form, or
report (see the OLH for the DatePart() function). And repeating tables
with identical structures (FY97, FY98,...,FYxx, all containing the
fields EmployeeID, date, hours, payrate) are also a violation of
database normalization rules. Your basic table design (assuming there is
some kind of identifier like EmployeeID, and some kind of primary key,
in there) is fine. I'm also assuming you also have a separate Employees
table (if not, you certainly should). With just these two tables,
related 1-m (each Employee appears only ONCE in tblEmployees, but
multiple times in tblPayroll, since each Employee is paid in multiple
pay periods [one would hope!]), you can easily retrieve the information
you want in the desired form. And it means not having to rebuild your
tables from scratch every year.

Hope this helps. Post back if you have any additional questions.

Good luck!

LeAnne

 

[Eric]

The reason why I was asking about maximum number of fields
is as follows..

I wanted to enter medical chart records for various clients

Since there is a seperate medical record for each client -
it seemed to make sense to try to put this all in one
table..

Unfortunately, there are over 255 bits of information on
each medical record.

Is this one of those RARE occassions when it might be
necessary to split a table into two parts - and
normalization won't help?

Eric

 

[LeAnne]

The reason why I was asking about maximum number of fields
is as follows..

I wanted to enter medical chart records for various clients

Since there is a seperate medical record for each client -
it seemed to make sense to try to put this all in one
table..

Unfortunately, there are over 255 bits of information on
each medical record.

Is this one of those RARE occassions when it might be
necessary to split a table into two parts - and
normalization won't help?

Good heavens, NO. See my reply downthread to Susan. You are making the
common error of organizing your data tables based on your desired end
product...a list (presumably a report) of each visit and each procedure
for each patient, which is what comprises a patient's record. Step back
for minute and think about your entities and how they are related. Each
client/patient can visit the office one OR MORE times. Each visit may
involve one OR MORE procedures, tests, etc. And each procedure, test,
etc. can be performed on one OR MORE visits. So you need at least four
tables: tblPatients (PatientID, FirstName, LastName, StreetAddress,
City, State, Zip, HomePhone...); tblVisits(VisitID, PatientID, ApptDate,
ApptTime, DrName (or DrId if you have a separate Physicians table)...);
tblProcedures (ProcedureID, ProcedureName, ProcedureMethod,
EquipNeeded,...); and tblVisitProcedures (VisitID, ProcedureID, Results,
RecTreatment,...) to resolve the many-to-many relationship between
visits and procedures. You can then generate reports not only on a
patient-by-patient basis ("Show me all procedures for patient John Q.
Smith grouped by visit") but also by date ("All patients in 2003"), by
procedure ("All patients who have ever had blood drawn"), or however you
prefer, with a few simple queries.

Hope this helps! Post back if you have additional questions.

Good luck,

LeAnne

 

[John Vinson]

I wanted to enter medical chart records for various clients

Since there is a seperate medical record for each client -
it seemed to make sense to try to put this all in one
table..

Unfortunately, there are over 255 bits of information on
each medical record.

Is this one of those RARE occassions when it might be
necessary to split a table into two parts - and
normalization won't help?

No; it's one of the very common occasions where what appears to be a
wide-flat set of data is actually a many to many relationship.

You have many Patients (each with a chart).

You have many Measurements - blood pressure, temperature, etc. etc.

Each Patient has many measurements, each measurement is performed on
many patients.

I'd suggest a structure like this:

Patients
PatientID
LastName
FirstName
<other bio information>

Measurements
MeasurementID
Description <e.g. "blood pressure">
<perhaps other fields such as normal range, danger zone range>

ChartData
PatientID <link to Patients>
MeasurementTime <date/time of measurement>
Value

ChartData would be a very tall skinny table with one record every time
a patient's data for that particular measurement was taken.

 

[Steve Schapel]

Susan,

You should absolutely not have a separate field for each week. You
will need a table with these fields:
EmployeeID
PeriodEndDate
HoursWorked

You will need a separate table for Employees, and as regards PayRate,
assuming a person's payrate can change over time, you will need
another table with these fields
RateID
EmployeeID
PayRate
DateEstablished

As regards the design of your forms for management of this data, and
the queries and reports that you need to derive from them, that's
another topic... But I assure you that these will work best if your
data table structure is correct.

- Steve Schapel, Microsoft Access MVP

 

[TC]

You need to read:

http://support.microsoft.com/support/kb/articles/Q100139.ASP

HTH,
TC