Access design

[srs]

I'm currently working on a registration database were participants make there
1st, 2nd and 3rd choice for classes. I have a field for each class and we
place a 1, 2 or 3 in the field. Is there a way to limit the number of 1's,
that can be placed in the field. ex. if there are 25 - 1's entered in the
field that class is full.


I sometimes also have to type in the name for a class on a totally separate
database that has a field for class 1, 2, 3 etc and you type in the name of
the class. Is there a way to limit the number of names that can be typed in?
ex. if there are 25 - chess entered in the field that class if full.

 

[Beetle]

When you say you're "currently working on a registration database"
do you mean you are designing a new database? If so, then STOP, because
what you've described is very poor design. At the very least you should have
a table for Students, a table for Classes, and a third table to handle the
relationship between a student and the classes they take. If this is the case,
and you need advice on table structure, you can post back with more details
and someone can give you more detailed advice.

On the other hand, if this is an existing db, then you may be in a situation
where you have no choice but to use it (at least for now), in which case
you'll probabaly have to use something like a DCount function in the before
update event of a control to count the number of 1s, 2s, 3s, etc. before a
record
is added. Again if you post back with more details, someone may be able to
offer more detailed advice

 

[Tom van Stiphout]

On Mon, 10 Mar 2008 19:30:01 -0700, srs

You should have a ROW for each class, not a field. That violates
normalization rules, which will later (or already) get you in trouble.
The new design:
ParticipantID
StudentID
ClassID
PreferenceNo

You can test for the quantity to be exceeded in the Form_Before update
event: run a Totals query to get the count, and set Cancel=True if at
the max. If you were using SQL Server, you could implement the check
in an Insert trigger.

-Tom.

 

[srs]

This is an existing database. I will try the dcount.