Student count for Class Registration

[Dennis]

Hi,


The goal / Issue:

I want to be able to register the student right after the user enters the
student name and address. Currently, the user does this by simply clicking
on the Registration tab and registering the student.

While I know the maximum number of students allows in the class, I don’t
know how to check the current count to determine if the class is open or
closed based on the student count.

Normally, I would cheat and have a field which is a count of the current
number of registered students. But I was hoping that is a better way to do
this in Access.


Does anyone have any suggestions?

Since this is both a database question and a data entry form question, I am
going to try to post it in both forums. I don’t know if I know how to do it
correctly, but I’ll give it a shot.


Back ground:
----------------

I’m working on a class registration module for the local library. They offer
some small arts and craft and other such classes. Class sizes range from 5
to 15 people. The average person takes between one and three classes. The
most classes any one person has ever taken is 7 (based on three years worth
of history).

At the present time I have a student form where the users enter the
student’s name and address. On a separate page (tab) within the student name
& address form I have a “continuous form†subform that allows displays all of
the classes that the student has taken and allows them to register the
student for a new class.


Database structure:
-----------------------

tblStudent
Key – StudentNo – Automatically assigned system number
Name
Address
Etc.

tblClassName
Key – ClassName – Abbreviation for the class
Fld – Long Description
- Short Desc

tblClass
key – Class Number – Automatically assigned system number
ClassName – Fk to the tblClassName tbl
ClassDate
ClassTime
ClassLoc FK to tblLocation – room number
StateTime
MaxNoStudents


tblClassReg
key – RegistrationNo – Automatically assigned number.
Fld – StudentNo FK to tblStudent
ClassNo FK to tblClass



I tried to provide enough detail for the question without going overboard.
If I did not provide enough detail, please let me know and I will be happy to
provide more.

Dennis

 

[Dennis]

All,

I think I figured it out. I created a query call tblClass_Count that gives
me the current number of students in each class.

SELECT qrytblClassD.ClassNo, qrytblClassD.ClassDesc,
Count(qrytblClassReg.CustAcctNo) AS CountOfCustAcctNo
FROM qrytblClassD LEFT JOIN qrytblClassReg ON qrytblClassD.ClassNo =
qrytblClassReg.ClassNo
GROUP BY qrytblClassD.ClassNo, qrytblClassD.ClassDesc
ORDER BY qrytblClassD.ClassNo, Count(qrytblClassReg.CustAcctNo);


On the continuous form sub-form, I have a combo box where I select the class
that the student wants to take. The current row source for it simply selects
the tblClass table. I will change that combo box row source to the above
query and use the ".Column()" value to retrieve the current student count.

I also realize that I will have to refresh this combo box after I add a
student.

At this time I only have one, maybe two people registering student and it
only happens a couple of times a day. So the timing issues of overbooking is
not an issue.

However, let's say it was an issues. Let's say that two different people
wanted to book someone different into the last seat in the class. Given the
above structure, how would you prevent that from happening?

Thanks,


Dennis