255 fields in a table. Is there any way to increase this?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My problem is that, I need to design a database that will track all the
training that takes place in my organization.

I have 1000 employees and about 1000 training classes a year. The data must
be collected and broken down in numerous manners, including team section and
job title.

I really need to be able to increase the number of allowable fields in the
access table. I want (would be really nice) to have a field for each
individual employee.


Help! This one has got me stumped. Any suggestions?
 
denandvicaperrin said:
My problem is that, I need to design a database that will track all
the training that takes place in my organization.

I have 1000 employees and about 1000 training classes a year. The
data must be collected and broken down in numerous manners, including
team section and job title.

I really need to be able to increase the number of allowable fields
in the access table. I want (would be really nice) to have a field
for each individual employee.


Help! This one has got me stumped. Any suggestions?
Click to expand...

No. Your tables should be...

Employees
EmployeeID
(additional fields describing an employee)

Classes
ClassID
(additional fields describing classes)

EmployeeClasses
EmployeeID ClassID

This three table structure lets you have as many employees attending as many
classes as you like and none of the tables will require very many fields. It
also requires no design changes when you add new classes (your design does
doesn't it?).

The axiom is "Columns are expensive. Rows are cheap" Any time you find
yourself thinking you need a table with lots of columns the design is almost
certainly incorrect.
 
What you really need to do is step back and normalize your data. Looking at
what you want to do, you probably need the following tables:

Employees (fields for EmployeeID, LN, FN, etc, etc)
Class (fields for ClassID, Description, etc)
ClassShed (fields for SchedID, ClassID, Date/Time, Location, etc)
EmployeeClassSchedAttend (fields for EmployeeID, SchedID, Attended,
comments, etc)

It isn't that you need more fields...you need to organize your data
logically so that you won't be adding fields constantly, but adding records
instead.

New Employee? Add a record. New Class? Add a record. New Class Schedule?
Add a record. etc.
 
What Rick and Paul are referring to is called a many-to-many relationship.
Each employee can take multiple classes and each class can be taken by
multiple employees. After you create the tables as they have described, you
link the three tables in the Relationships window with two one-to-many
relationship links. The new, middle table they have described will be on the
many side of each link. Link each of the other two tables from their ID
field to the associated ID field in the linking table. Using Rick's example,
you would link EmployeeID from the Employees table to EmployeeID in the
EmployeeClasses table and you would link ClassID from the Classes table to
ClassID in the EmployeeClasses table.
 
This is a design issue. Its not that you need additional fields, but
rather you need to modify the database scheme. Think of it as you're
trying to put more and more sheets of paper into a single file folder
and you can close the file folder. The solution isn't to put a rubber
band around it. The solution is to break apart the single folder into
multiple folders that better organize the data.

For starters,
You'll need several tables...

tblEmployees
lngEmployeeNumber
txtEmployeeNameFirst
txtEmployeeNameLast
lngTeamDescription
lngJobPosition

tblTrainingClassesMaster
lngTrainingClassId
txtTrainingClassDescription

tblTrainingClassesAttendance
lngRecordId
lngTrainingClassId
lngEmployeeNumber

tblJobPositions
lngJobPosition
txtJobPositionDescription

Second I would HIGHLY recommend pickingup the Access Developer's
Handbook by Sybex.
 
What you really need to do is step back and normalize your data. Looking at
what you want to do, you probably need the following tables:

Employees (fields for EmployeeID, LN, FN, etc, etc)
Class (fields for ClassID, Description, etc)
ClassShed (fields for SchedID, ClassID, Date/Time, Location, etc)
EmployeeClassSchedAttend (fields for EmployeeID, SchedID, Attended,
comments, etc)

It isn't that you need more fields...you need to organize your data
logically so that you won't be adding fields constantly, but adding records
instead.

New Employee? Add a record. New Class? Add a record. New Class Schedule?
Add a record. etc.
Click to expand...
 
??? No....I wasn't thinking of Excel...and I'm very much aware of Access'
capabilities.
 
Wayne Morgan said:
What Rick and Paul are referring to is called a many-to-many relationship.
Each employee can take multiple classes and each class can be taken by
multiple employees. After you create the tables as they have described, you
link the three tables in the Relationships window with two one-to-many
relationship links. The new, middle table they have described will be on the
many side of each link. Link each of the other two tables from their ID
field to the associated ID field in the linking table. Using Rick's example,
you would link EmployeeID from the Employees table to EmployeeID in the
EmployeeClasses table and you would link ClassID from the Classes table to
ClassID in the EmployeeClasses table.
Click to expand...

Thank you for your time. This advice was in invaluable. It opens a whole
new world in the Access database scheme.


Dennis
 
Back
Top