Too Many Fields

[Marc C]

I have a problem and need some suggestions how to handle
this. I have developed a database that workers use as an
assessment. I have normalized the data as much as
possible. The problem is that I have a huge amount of
yes/no boxes for counsellors to check off in each specific
area. I need to be able to have checkboxes or something
that will allow users to select multiple items. A combo
box would not work because that allows only one
selection. All of these yes/no boxes are sucking up
valuable field space and I am just about at my 255 limit.
Any suggestions?

 

[Tim Ferguson]

I have normalized the data as much as possible.

No you haven't.

Check out At Your Survey on

<http://www.rogersaccesslibrary.com/duanehookom/duanehookom.htm>


Best wishes


Tim F

 

[Immanuel Sibero]

I have a problem and need some suggestions how to handle
this. I have developed a database that workers use as an
assessment. I have normalized the data as much as
possible. The problem is that I have a huge amount of
yes/no boxes for counsellors to check off in each specific
area. I need to be able to have checkboxes or something
that will allow users to select multiple items. A combo
box would not work because that allows only one
selection. All of these yes/no boxes are sucking up
valuable field space and I am just about at my 255 limit.
Any suggestions?

Yep, normalize further.

You didnt disclose the table design. But if I were to guess, whereever you
have the field sucking yes/no boxes, they dont belong there. Creating a
yes/no field for *each specific area* is not the way to do it.

HTH,

Immanuel Sibero

 

[John Nurick]

Hi Marc,

There's one big normalisation step still to take. In all those yes/no
boxes you are effectively storing data in the field names. And I'll bet
that not every yes/no item is relevant to every client.

At present you have in effect a list of - I don't know just waht they
are but let's call them - AssessmentItems. Normalise them into a table
of their own. Suppose you now have fields in your table called
Stage1OK, Stage2OK, Stage3OK, SupervisorCheck1, Stage4OK ...
These should each be records in a new table

tblAssessmentItems
AssessmentItemID (primary key)
AssessmentItemName

with data like
1,"Stage 1 Completed"
2,"Stage 2 Completed"
3,"Stage 3 Completed"
4,"First supervisor approval"
5,"Stage 4 Completed"

You now have two entities - Client and AssessmentItem - with a
many-to-many relationship (each Client has many assessments and each
assessment is applied to many clients). Implementing this in a
relational database such as Access neeeds a joining table

tblClientAssessments
ClientID (foreign key into main Clients table)
AssessmentItemID (foreign key into tblAssessmentItems)
(both fields in primary key)
Result (Yes/No)

Then, the fact that Client 999 had passed Stage3 would be stored as a
record in tblClientAssessments
999,3,-1
and so on.

This setup totally frees you from the 255-field limit, and also means
that you don't have to restructure your tables any time someone invents
a new assessment or revises the process.

 

[Marc Noon]

What types of information does the groups represent?

Regards,
Marc Noon

 

[Marc Noon]

If you have a bunch of yes and nos. You could use a byte to represent the
group. For a specific field. Then in vb you could have a form for each
group. This would allow you do a search on 8 specific questions per group.
Otherwise you could use a integer, or a long depending on what you wanted to
do with it.

Then for each yes or no anser the first bit would represent you first
yes/no. The second bit would present your second yes/no. etc...

Then if you do a query, you can do binary searches using the "and" function
to match all records with one setting. For instance, in a particular
group you selected Yes for database nut, you could then find all records
where your students were database nuts by anding it with binary 0000 0100
since th 3rd question in the group is what you are searching for.

Then you should get a matching number if your and was successful for that
record... which would look something like if ((11110111 AND 00000100) =
00000100) then whatever... I can't remember what the binary funcion in in
vb, but good luck. Of course you would use the Hex function to do this. If
you don't use hex, just remember that 1, 2, 4, 8, 16, 32, 64, 128, 256 etc
represent your individual bits, and if you do this in hex, it much easier to
do the binary arithmetic in your head. If you use this, every group can be
specific to one field. You'll need to simply decouple the data in your form
using vb ...

HTH,
Marc Noon