form with "live" count

[Guest]

Running Access 2000, soon to be 2002. Have simple database tracking class
attendance. Look to extend use to include recording registration data. A
major issue I need to resolve.

Class size is limited to 10. Is there a way to include a "live" query,
report, or subform on a form that would count total number of registrations
for a specific class and issue any type warning or notice when max limit is
reached? Registartions are entered as they arrive via mail or email, so not
all entries are made at one time.

Suggestions for an Access novice, please?

 

[Guest]

At some point in your database, you will have a way to add a new registration
to a class. Without knowing how your registration form will be structured,
it will be difficult for me to tell you exactly where it shoud go, but here
is how it works.


If Nz(DCount("*", "tblClassRegs", "[CLASS_ID] = '" & Me.cboClass.Column(1) _
& "'"),0) _ < 10 Then
'It is okay to continue with this registration
Else
MsgBox "This Class is Full",
End If

the Nz function will prevent a Null value if no matches for the DCount
function are found

"*" Is used when you don't need to count a specific field in the table

tblClassRegs is the name of the table where you would keep who is registered
in which class. We will discuss the correct relational set up later.

[CLASS_ID] would be the field in tblClassRegs that identifies the Class you
are trying to set a a registration for.

Me.cboClass.Column(1) is the value in the second column of the currently
selected row of a combo box that lists all the available classes. This combo
should be based on a query that returns the CLASS_ID and the CLASS_NAME. We
will cover this next in the relational discussion.

So the logic of the statement is, If fewer than 10 students are already
registered in the selected class, it is okay to register the student;
otherwise, a warning message apprears to let you know the class is full.

To do this correctly, you will need 3 tables:
tblStudent - All prospective and registered students
tblClass - All offered classes
tblClassRegs - A table to contain which students are registered in which
classes.
Since there are many students who will be registered in many classes, a many
to many relationship exists between these two tables. Many to many
relationships do not work in a relational database model. The correct
solution is to create a cross reference table, in this cals tblClassRegs.
So, one student is registered in zero to many classes. This makes tblStudent
a one to many relation to tblClassRegs. A Class may have from 0 to many
students registerd. This makes tblClassRegs a one to Many to tblClass.
To keep the relationships together, each table should have an Autonumber
field that is the primary key for the table.
tblStudent - STUDENT_ID
tblClass - CLASS_ID
tblClassRegs - REG_ID

The fields tblClassRegs needs are
REG_ID
STUDENT_ID
CLASS_ID

So, to create the combo box recordsource, you could create a query that
would join tblClassRegs with tblClass, and the columns you would use would be
CLASS_ID from tblClassRegs and CLASS_NAME from tblClass. CLASS_ID would be
the bound field, but not visible, and CLASS_NAME would be visible for the
user's convenience.

I hope some of this will help.

 

[Guest]

Thanks for the walk through, that will help as I move forward with the
concept. I was thinking of an actual listing of teh students registered, but
your idea works great. I already have several of your suggestions in place, a
classid table, student table, and a tranasction table. The one addition I
expect to have to put in place is a Session table recording date and time of
each session, Currently this gets entered for each registration. Clunky I
know, but just haven't finalized yet.

Thanks agian for getting me headed in the right direction!

At some point in your database, you will have a way to add a new registration
to a class. Without knowing how your registration form will be structured,
it will be difficult for me to tell you exactly where it shoud go, but here
is how it works.


If Nz(DCount("*", "tblClassRegs", "[CLASS_ID] = '" & Me.cboClass.Column(1) _
& "'"),0) _ < 10 Then
'It is okay to continue with this registration
Else
MsgBox "This Class is Full",
End If

the Nz function will prevent a Null value if no matches for the DCount
function are found

"*" Is used when you don't need to count a specific field in the table

tblClassRegs is the name of the table where you would keep who is registered
in which class. We will discuss the correct relational set up later.

[CLASS_ID] would be the field in tblClassRegs that identifies the Class you
are trying to set a a registration for.

Me.cboClass.Column(1) is the value in the second column of the currently
selected row of a combo box that lists all the available classes. This combo
should be based on a query that returns the CLASS_ID and the CLASS_NAME. We
will cover this next in the relational discussion.

So the logic of the statement is, If fewer than 10 students are already
registered in the selected class, it is okay to register the student;
otherwise, a warning message apprears to let you know the class is full.

To do this correctly, you will need 3 tables:
tblStudent - All prospective and registered students
tblClass - All offered classes
tblClassRegs - A table to contain which students are registered in which
classes.
Since there are many students who will be registered in many classes, a many
to many relationship exists between these two tables. Many to many
relationships do not work in a relational database model. The correct
solution is to create a cross reference table, in this cals tblClassRegs.
So, one student is registered in zero to many classes. This makes tblStudent
a one to many relation to tblClassRegs. A Class may have from 0 to many
students registerd. This makes tblClassRegs a one to Many to tblClass.
To keep the relationships together, each table should have an Autonumber
field that is the primary key for the table.
tblStudent - STUDENT_ID
tblClass - CLASS_ID
tblClassRegs - REG_ID

The fields tblClassRegs needs are
REG_ID
STUDENT_ID
CLASS_ID

So, to create the combo box recordsource, you could create a query that
would join tblClassRegs with tblClass, and the columns you would use would be
CLASS_ID from tblClassRegs and CLASS_NAME from tblClass. CLASS_ID would be
the bound field, but not visible, and CLASS_NAME would be visible for the
user's convenience.

I hope some of this will help.

Running Access 2000, soon to be 2002. Have simple database tracking class
attendance. Look to extend use to include recording registration data. A
major issue I need to resolve.

Class size is limited to 10. Is there a way to include a "live" query,
report, or subform on a form that would count total number of registrations
for a specific class and issue any type warning or notice when max limit is
reached? Registartions are entered as they arrive via mail or email, so not
all entries are made at one time.

Suggestions for an Access novice, please?

 

[Guest]

In that case, expand the tblClassRegs to include that information about the
registrations. You will need a table replated to tblClass to define session
date, time, and perhaps, location.

Thanks for the walk through, that will help as I move forward with the
concept. I was thinking of an actual listing of teh students registered, but
your idea works great. I already have several of your suggestions in place, a
classid table, student table, and a tranasction table. The one addition I
expect to have to put in place is a Session table recording date and time of
each session, Currently this gets entered for each registration. Clunky I
know, but just haven't finalized yet.

Thanks agian for getting me headed in the right direction!

At some point in your database, you will have a way to add a new registration
to a class. Without knowing how your registration form will be structured,
it will be difficult for me to tell you exactly where it shoud go, but here
is how it works.


If Nz(DCount("*", "tblClassRegs", "[CLASS_ID] = '" & Me.cboClass.Column(1) _
& "'"),0) _ < 10 Then
'It is okay to continue with this registration
Else
MsgBox "This Class is Full",
End If

the Nz function will prevent a Null value if no matches for the DCount
function are found

"*" Is used when you don't need to count a specific field in the table

tblClassRegs is the name of the table where you would keep who is registered
in which class. We will discuss the correct relational set up later.

[CLASS_ID] would be the field in tblClassRegs that identifies the Class you
are trying to set a a registration for.

Me.cboClass.Column(1) is the value in the second column of the currently
selected row of a combo box that lists all the available classes. This combo
should be based on a query that returns the CLASS_ID and the CLASS_NAME. We
will cover this next in the relational discussion.

So the logic of the statement is, If fewer than 10 students are already
registered in the selected class, it is okay to register the student;
otherwise, a warning message apprears to let you know the class is full.

To do this correctly, you will need 3 tables:
tblStudent - All prospective and registered students
tblClass - All offered classes
tblClassRegs - A table to contain which students are registered in which
classes.
Since there are many students who will be registered in many classes, a many
to many relationship exists between these two tables. Many to many
relationships do not work in a relational database model. The correct
solution is to create a cross reference table, in this cals tblClassRegs.
So, one student is registered in zero to many classes. This makes tblStudent
a one to many relation to tblClassRegs. A Class may have from 0 to many
students registerd. This makes tblClassRegs a one to Many to tblClass.
To keep the relationships together, each table should have an Autonumber
field that is the primary key for the table.
tblStudent - STUDENT_ID
tblClass - CLASS_ID
tblClassRegs - REG_ID

The fields tblClassRegs needs are
REG_ID
STUDENT_ID
CLASS_ID

So, to create the combo box recordsource, you could create a query that
would join tblClassRegs with tblClass, and the columns you would use would be
CLASS_ID from tblClassRegs and CLASS_NAME from tblClass. CLASS_ID would be
the bound field, but not visible, and CLASS_NAME would be visible for the
user's convenience.

I hope some of this will help.

Running Access 2000, soon to be 2002. Have simple database tracking class
attendance. Look to extend use to include recording registration data. A
major issue I need to resolve.

Class size is limited to 10. Is there a way to include a "live" query,
report, or subform on a form that would count total number of registrations
for a specific class and issue any type warning or notice when max limit is
reached? Registartions are entered as they arrive via mail or email, so not
all entries are made at one time.

Suggestions for an Access novice, please?