Table with 230 Fields....what's a better alternative?

[Regnab]

I'm making a database to record training that employees have recieved.
For each training excercise, they recieve a mark between 1 and 3.

Initially, when there weren't so many training elements, I had put the
employee first name, last name, section, First Aid, Forklift
operation... and so on in one table. However, I now have a list of 227
Training elements that need to be completed (each recieving a mark
between 1 and 3). When I tried to put them all in one table, it said
there were too many fields.

Then I split them up into two tables and tried to join them with a
query based on first and last name. That produced all the fields I
needed, but did not show all the combo boxs (most, but not all) that I
had specified in the table set-up (Combo Box, Value List, "1","2","3").
Now I'm thinking I'll need to break them down into multiple smaller
tables, and display the fields via multiple subforms on a master form.

I'd like to know if people have a better solution. Actually, I'm pretty
sure someone does. Not even sure my idea of splitting them further will
work, but its the best I can come up with at the moment...

Thanks for your help,

Reg

[Brian Wilson]

"Regnab" <p.*******@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

I'm making a database to record training that employees have recieved.
For each training excercise, they recieve a mark between 1 and 3.

Initially, when there weren't so many training elements, I had put the
employee first name, last name, section, First Aid, Forklift
operation... and so on in one table. However, I now have a list of 227
Training elements that need to be completed (each recieving a mark
between 1 and 3). When I tried to put them all in one table, it said
there were too many fields.

Then I split them up into two tables and tried to join them with a
query based on first and last name. That produced all the fields I
needed, but did not show all the combo boxs (most, but not all) that I
had specified in the table set-up (Combo Box, Value List, "1","2","3").
Now I'm thinking I'll need to break them down into multiple smaller
tables, and display the fields via multiple subforms on a master form.

I'd like to know if people have a better solution. Actually, I'm pretty
sure someone does. Not even sure my idea of splitting them further will
work, but its the best I can come up with at the moment...

Thanks for your help,

Reg

Your current design has the fundemental problem that to add a new training
element, you need to add a new field. This means you need to change not
only table structure, but queries, forms, reports, modules, etc. To make
things much easier, you need to set up your table structure so it can cope
with adding/editing/deleting training elements without any need to change
any of the forms, reports, etc.
The standard solution is to have three tables:
tblEmployee - 1 record per employee
tblTraining - 1 record per training element
tblEmployeeTraining - 1 record for each bit of training an employee has done

This is the only sensible way to do this, but will involve pulling apart
almost all you have done and converting the data from the old to the new
format. However, there is not much point describing this technique further
unless we know you would be happy to take this radical re-design. Let us
know.

[Brian Wilson]

"Regnab" <p.*******@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

I'm making a database to record training that employees have recieved.
For each training excercise, they recieve a mark between 1 and 3.

Initially, when there weren't so many training elements, I had put the
employee first name, last name, section, First Aid, Forklift
operation... and so on in one table. However, I now have a list of 227
Training elements that need to be completed (each recieving a mark
between 1 and 3). When I tried to put them all in one table, it said
there were too many fields.

Then I split them up into two tables and tried to join them with a
query based on first and last name. That produced all the fields I
needed, but did not show all the combo boxs (most, but not all) that I
had specified in the table set-up (Combo Box, Value List, "1","2","3").
Now I'm thinking I'll need to break them down into multiple smaller
tables, and display the fields via multiple subforms on a master form.

I'd like to know if people have a better solution. Actually, I'm pretty
sure someone does. Not even sure my idea of splitting them further will
work, but its the best I can come up with at the moment...

Thanks for your help,

Reg

Your current design has the fundemental problem that to add a new training
element, you need to add a new field. This means you need to change not
only table structure, but queries, forms, reports, modules, etc. To make
things much easier, you need to set up your table structure so it can cope
with adding/editing/deleting training elements without any need to change
any of the forms, reports, etc.
The standard solution is to have three tables:
tblEmployee - 1 record per employee
tblTraining - 1 record per training element
tblEmployeeTraining - 1 record for each bit of training an employee has done

This is the only sensible way to do this, but will involve pulling apart
almost all you have done and converting the data from the old to the new
format. However, there is not much point describing this technique further
unless we know you would be happy to take this radical re-design. Let us
know.

[Regnab]

Brian,

I appreciate any help you can give me and considering I've restarted
this project twice already, once more won't hurt :). I had realised
that the structure was very limiting in so far as updating training
etc, but couldn't think of how else to do it. Your idea sounds
good....please explain :).

[pietlinden]

This is a basic students-classes database.

Student(StudentID*, FirstName, LastName...)
SectionRoster(StudentID*,SectionID*, Grade)
Section(SectionID*,CourseID, InstructorID, StartTime, EndTime)
Course(CourseID, CourseTitle,DepartmentID)
Department(DepartmentID, DepartmentName)

Documented absolutely to death. It's in Elmasri's book, I'd swear.
(Fundamentals of DB Systems), and probably a zillion other places...

Search for the right design on the web somewhere. Test it a little.
If you build the whole thing and it's wrong, getting
grades/averages/whatever is going to be a complete nightmare. IF you
do it right, itshouldbe easy.

[Regnab]

I think I get the idea of what you're getting at...

tblEmployee (EmployeeID, Firstname, LastName)
tblTraining(TrainingID, TrainingDesc)
tblEmployeeTraining(EmployeeID, TrainingID, TrainingScore)

This makes good sense to me, but not sure exactly how you would make up
the third table. Normally I would make up a form and the user would put
data straight into it - enter "John" into name field, and it is
recorded under FirstName in tblEmployee. How would they enter a
Training Score when the first 2 values (EmployeeID, TrainingID) need to
be input into the record as well? I could do it via an SQL insert query
but I'm assuming you boys have a better and easier way. After all,
there's 227 training records one employee could have a score against.

What I'm trying to get at is how do you design the Form to allow data
entry. At the moment I'd like to have the Employee name at the top,
with a list of training options below, with a combo box for each
TrainingDesc offering the different scores available.

Thanks for your help,

Cheers

Reg

[Brian Wilson]

"Regnab" <p.*******@gmail.com> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...

I think I get the idea of what you're getting at...

tblEmployee (EmployeeID, Firstname, LastName)
tblTraining(TrainingID, TrainingDesc)
tblEmployeeTraining(EmployeeID, TrainingID, TrainingScore)

This makes good sense to me, but not sure exactly how you would make up
the third table. Normally I would make up a form and the user would put
data straight into it - enter "John" into name field, and it is
recorded under FirstName in tblEmployee. How would they enter a
Training Score when the first 2 values (EmployeeID, TrainingID) need to
be input into the record as well? I could do it via an SQL insert query
but I'm assuming you boys have a better and easier way. After all,
there's 227 training records one employee could have a score against.

What I'm trying to get at is how do you design the Form to allow data
entry. At the moment I'd like to have the Employee name at the top,
with a list of training options below, with a combo box for each
TrainingDesc offering the different scores available.

Thanks for your help,

Cheers

Reg

Those are exactly the right questions to be asking. Almost all developers
would use the same type of 3-table structure to model the situation you
describe, but how do you view and edit the data? Here you could offer a
number of options depending on how much customisation/vba coding you are
prepared to do.
At its simplest, you use a subform with the master/child field set to
EmployeeID. This approach means that the correct training records are
displayed in the subform whenever you move to a new employee on the main
form. It also means that with any additions, the EmployeeID is
automatically added. So now you only need to add the TrainingID and the
score. The TrainingID is added by using a combobox which displays the name
but stores the ID (2 columns with the first set to zero-width). The score
is just a textbox.
OK, that's the simplest but it does have some problems. A combobox with
200-300 entries means that if someone was looking for 'Forklift' but it had
been written as 'Category A Forklift' it would be hard for the user to find
this in all those entries. Better would be where you press a button and
type 'forklift' and all possible matches were shown allowing the user to
select between a few.
There is also the problem that if a user has completed many types of
training, then displaying them in one large list might not be that easy to
look through. You could add a field to indicate TrainingType which might
come from another table such as:
1 Warehouse Skills
2 Health and Safety
3 IT Skills
Then you could break up the long list of training into easy to see bits. If
you needed to do some analysis of these training courses, then you could
even code up the courses so that you allocated a number of digits to
indicate the course content, e.g. digit1=type, digit2=compulsory or not,
etc. This type of 'masked field' allows for fast and flexible analysis, but
may be overkill for the start.

If you want some example sent by e-mail, just let me know if your posted
e-mail is valid (mine isn't) and I'll try and send you something.

[pietlinden]

What I'm trying to get at is how do you design the Form to allow data
entry. At the moment I'd like to have the Employee name at the top,
with a list of training options below, with a combo box for each
TrainingDesc offering the different scores available.

Thanks for your help,

Hmm...
"Have the employee name at the top" - so make the training form a
subform inside the Employee form.

"with a list of training options below,"
Umm... how about a continuous subform where you can choose the training
course from a combobox? And if you need to filter it, you can put an
UNBOUND textbox on the main form, and then set the rowsource of the
combobox to something like

Like Me.parentForm.txtFilter & "*"

and then you can come up with a manageable number of values in your
combobox. You can show pretty much whatever you want in your combobox
- just make it multi-column and show the ones you want to show. So you
have something like:

SectionID CourseNo CourseName DateOfTraining

as your combobox rowsource and you hide column(0), SectionID. then you
can filter on courseNo or whatever...

[Regnab]

Brian, my email is as above, and I'd appreciate it if you could send me
examples. I'm trying to teach myself Access so any models will be very
useful.

I'll try out what yourself and Pietlin have suggested tonight and see
how I go. Ideally, I'd like to be able to have all the training records
(even those not completed by that employee) listed - allowing the user
to peruse the form and see what has and hasn't been done. To make it
easier to identify which training is required, I was going to divide
them into sections which would be put on a Tab Control - with section
names along the top (ie Mill, Administration). The user could click on
each tab and see what further training an employee would need to work
in each section.

Anyways, just thought I'd explain it a little more. if you could send
me the examples, that would be much appreciated.

Regards

Reg

[Regnab]

Brian,

Thanks for the demos. It's pretty much exactly what I need to do. The
only contigency I was wondering about was when one training record is
associated with multiple departments - ie you want "First Aid" to
appear on 2 different tabs. I haven't chewed it over properly yet so
will hopefully work it out but just thought I'd mention it.

Cheers

Reg

[Regnab]

Have worked out the one to many issue with the training...simple I
know...