Limit number of records

[JimISManager]

I am trying to create a database for a music summer camp. This camp has a number of classes that have a limit of 10 or 15 people. How can I create the tables so that:
1. It will only allow a certain number of people to be allowed in the class,

and

2. It will not allow one person to be scheduled for more than one class at the same time on the same day.

I appreciate any ideas/help.

 

[batteam]

Think about this:

The important thing to all this is to set up your tables correctly. At the very least: have a table of student data with each student assigned a unique ID and other pertinent data, have another table with offered classes and assign each class a unique ID and the offered time block and day of the week, etc., a third table of 'Registered students' that contains only records of students properly enrolled in class(es), again, containing all the pertinent data.

Be sure your table relationships are set up properly, etc., etc.

For #1
Each time a student registers for a class, do a DCount (like on student ID) on the table that holds the data showing students already registered for that class. If the count comes up with > the class size limit, send a msg to the user that the class is full.

For #2
Have the table that contains each student's schedule of classes also contain the day of the week and the time block period it occurs in. Then, when someone attempts to register for a class, look up that class's day and time block in the class table. Then, with that info, do a search on that student's current class schedule and compare the student's ID, day of the week and time block for each record that that contains the Student ID. If you get a match on all three (3), send a message to the user that he/she cannot register for that class because of a scheduling conflict.

Hope this gives you some initial direction. Good Luck.

 

[willir]

JimISManager

Jim, this more of a table design issue (but i am sure you jeard that song before ;-) )

There are two approaches, and depends on the question: Can a student attend more than one class at a time? At high school or university, a course is offered at different times in different rooms. A student takes many courses, and each class and each course has many students.

If no, then you have a one-to-many relationship (1:M) between students and class. A class can have many students. A student can only be in one class.

If yes, then you have a many-to-many relationship (M:M).

I will try to make it simple, and avoid complexity where possible. I am going to give you the one-to-many solution. But since you are designing a database to handle this task, I will assume you offer the programs more than once and have enough student to warrant the work. Here goes...

StudentTbl
StudentID - primary key, autonumber
StudentLN - text (last name)
StudentFN - text (first name)
PhoneNum - text
Address - text
Alert - Y/N field
AlertComment - Memo - tracks alert message - allergies, type of thing.
plus other stuff sutiable for the student

If you have several instructors...
TeacherTbl
TeacherID - primary key, autonumber
TearchName - text (you can use last name and first name fields)
PhoneNum - text
Address - text

CourseTbl
CourseID - primary key, autonumber OR Course Code text field
CourseDesc - text field
DateStart - date field, using short or medium date format
DateEnd - date
TimeStart - date field, using a suitable time format
TimeEnd - date
TeacherID - long interger, foreign key to TeacherTbl
ClassLimit - interger
Cost - currency
plus other stuff suitable for the course


ClassTbl
CourseID - long interger, foreign key to CourseTbl
StudentID - long interger, foreign key to StudentTbl

The primary key for this table is CourseID + StudentID

This will prevent a student from registery twice for the same course.

To prevent the system from over booking, when registering the students, use the event procedure "Before Insert" to count the current number of registered users and compare it against the ClassLimit. You can set it up so that you can not register beyond the limit, or allow the person registering to over ride the limit.

There is one issue to bring to your attention. I broke one of the rules for normalization here (process for designing relational databases), when I setup the Course and Class tables. I did this to simplify your work, but there is one ramification of this design. You have to enter a course for each scheduled class.

In a university setting, you would have one course with multiple classes. Each class would have it's own schedule, and it's own roaster. This is a better solution, but adds complexity. Here, the registrar could say I have 1,004 students in this course with about 333 in each trimester and about 100 in each class.

If you have Guitar 101, and offer it three times over the summer, with the structure I provided, you have to have Guitar 101 A, Guitar 101 B and Guitar 101 C, each with it's own description. To find out how many took Guitar 101, you would have to add the number students for each "course". I suspect you can live this limitation.

I did not touch receipt of payment. Not to hard to do for this type of system, but let's get this part working first.

In the future, forums to consider...
Tables and Relationships

http://www.tek-tips.com/threadminder.cfm?spid=700

Forms

http://www.tek-tips.com/threadminder.cfm?spid=702

(You probably will post here when you get enformcing limits to the class size)