Too Many Fields!

[Chaplain Doug]

I have a table that is used to records progress for people
in a five-week program. Each week they must complete
three process groups and five educational groups. I need
to somehow record whether these people have completed each
of the 40 requirements. There are 25 other fields I
already have in the table. If I use yes/no (check) fields
for each requirement, that's a total of 40 more fields,
like

W1PG1, W1PG2, W1PG3, W1EG1, W1EG2, ... and so on.

Is there a better way to do this than having forty
fields? Is there such a thing as a "field array" or
an "array field"? It just seems dorky to make so many
fields. My intuition tells me there must be a better
way. Any help will be appreciated. God bless.

 

[Bas Cost Budde]

I have a table that is used to records progress for people
in a five-week program. Each week they must complete
three process groups and five educational groups. I need
to somehow record whether these people have completed each
of the 40 requirements. There are 25 other fields I
already have in the table. If I use yes/no (check) fields
for each requirement, that's a total of 40 more fields,
like

W1PG1, W1PG2, W1PG3, W1EG1, W1EG2, ... and so on.

Is there a better way to do this than having forty
fields? Is there such a thing as a "field array" or
an "array field"? It just seems dorky to make so many
fields. My intuition tells me there must be a better
way. Any help will be appreciated. God bless.

Good thought! You need another table. Primary key for that table is the
current primary key *plus* the field that will contain the name of all
those W1PG1 fields; a next column will then contain all values.

So

PK, person, group, otherfield, W1PG1, W1PG2, W1PG3, W1EG1, W1EG2
1, john, deltawing, othervalue, true, false, ...

becomes

PK, requirement, value
1, W1PG1, true
1, W1PG2, false
etc

 

[John Vinson]

I have a table that is used to records progress for people
in a five-week program. Each week they must complete
three process groups and five educational groups. I need
to somehow record whether these people have completed each
of the 40 requirements. There are 25 other fields I
already have in the table. If I use yes/no (check) fields
for each requirement, that's a total of 40 more fields,
like

W1PG1, W1PG2, W1PG3, W1EG1, W1EG2, ... and so on.

Is there a better way to do this than having forty
fields? Is there such a thing as a "field array" or
an "array field"? It just seems dorky to make so many
fields. My intuition tells me there must be a better
way. Any help will be appreciated. God bless.

There is a better way to do this, indeed!

You're using a relational database - USE IT RELATIONALLY! "Fields are
expensive, records are cheap". Someday you might have five process
groups and eight educational groups and the problem will get even
worse.

I'd suggest the following tables:

People
PersonID
<bio information, name, etc.>

ProcessGroups
GroupNo <primary key>
Description

ProgramGroups
ProgramID <primary key>
ProgramDescription

ProcessCompletion
PersonID <link to People>
GroupNo <link to ProcessGroup>
DateCompleted
<anything else about this person/this process>

EducationCompletion
PersonID <link to People>
ProgramID <link to ProgramGroups>
DateCompleted
<anything else about this person/this program>

You could have a Form based on the People table, with subforms to
display which programs and processes they've completed.

 

[Duane Hookom]

You are, from the vocabulary of Jeff Boyce, "committing spreadsheet". What
would happen if a new process or educational group was added next week?
Having to change a table structure is a no-no.

I would normalize the tables so that a current check box becomes its own
record in a related table. This would allow users to add or modify
requirements as needed.
Think of your requirements as a survey. Then download At Your Survey from
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane to see
how this can be normalized.

Come on back if you have further questions.

 

[Chaplain Doug]

Thanks for the help guys. My brain is smoking already
thinking about having bunches of tables to design and to
keep track of. I guess its either bunches of fields or
bunches of tables? I do commit "spreadsheet" because I am
a linear thinker. Nonlinear produces short-circuit for
me. I will try to change to a "relational" view of
database design. I feel like a grampa. Why when I first
started as an engineer we used paper tape readers, card
readers, teletype (30 baud) input, and flew missiles with
embedded programs in 64K of memory period. I mean launch,
midcourse, and terminal navigation, guidance, and
targeting to land on a dime with 64K! Now file names
taken up 64K. Oh well. God bless.

 

[Duane Hookom]

I hope JV doesn't mind but neither he nor I would be described as young.
Compared to other grampas, I might be on the younger side.

 

[Kelvin]

Doug,

I may get grief about this, but here is a thought. If all you are doing is
keeping track of yes/no type information you can use what I call the
register aproach. This will require a little more coding, but I find it
easier to manage. Create a field for each week with the data type set to
text and set the default as "00000000". Each "0" represents each class.
When a person completes the class, change the "0" to a "1". You can then
check if that week's courses have been completed by checking if the field
equals "11111111". You could also have created one large field with 40 "0"s
but that gets hard to keep track of.

You can easily change the first "0" using a check box and an if statement
with something like Mid(txtClasses,1,1)="1" when the box is checked and
Mid(txtClass,1,1)="0" when unchecked. Counting the number of classes taken
will require a loop, but that would also be easy. If the number of classes
or the number of weeks changed, you would need to modify the design. Some
of this could be handled ahead of time by adding extra fields as space
holders for additional weeks and instead of using a default value, have them
set to "00000000" when student's record is created. You could also use code
to loop through each digit which could then handle fewer or more classes.
You could also normalize the weeks data which could handle different weeks
instead of haveing multiple fields. Just an idea.

Kelvin

 

[Guest]

Thanks Kelvin. I have been duly corrected in
my "spreadsheet" design of database tables. It made me
think, and I have had an attitude adjustment. I settled
for this project on having one integer field for each type
of class taken. I put a control that allows the user to
click and have the number increment. In this case they
are happy to just know how many classes of each type have
been completed. God bless all for the help!