Limit Number of Records

[DanRum]

I have setup a simple Registration page for classes in our facility and I want to limit the number of people that can be in the class and direct them to a html page that tells them the class is full. I also want to set the table to not allow and duplacate entry in them first and last name fields.

Thanks,
Danny

 

[onpnt]

first and last name will only need a simple validation step on the client side.
if (fistname == lastname) {
error
}

as for the max amoutn of records I'm assuming you have a autonumber for a id.
before inserting the values do a check
if rs("id&quot > 20 then
'error
'redirect to the html page
response.redirect "full.htm" ---------------------------------------
{ str = "sleep is good for you. sleep gives you the energy you need to function";
ptr = /sleep/gi;Nstr = str.replace(ptr,"coffee"alert(Nstr); }

http://www.onpntwebdesigns.com

 

[mwolf00]

Danny, I think that you are actually trying to make sure that the same person doesn't register for the class twice. I would suggest that you create a table for registrants and give each a unique ID (Primary key, autonumber/identity field). When adding registrants to the table, check to make sure that none are registered already (SELECT count(*) FROM tblRegitsrant WHERE firstName='John' AND lastName='Smith') - You should consider the possibilty that two ppl may have the same name, perhaps add a third key like phone number...

Now that you have ensured unique registrants with unique IDs you can check your class registry for that unique ID and number od registrants like this (SQLServer statment)..

SELECT count(ID), specificID FROM myClass JOIN (SELECT ID, specificID=count(ID) FROM myClass WHERE ID = (SELECT id FROM tblRegistrant WHERE firstName='John' AND lastName='Smith' GROUP BY ID) as derivedTable ON myClassID = derivedTable.ID -----------------------------------------------------------------
"Whether you think that you can, or that you can't, you are usually right."
- Henry Ford (1863-1947)

mikewolf@tst-us.com

 

[DanRum]

I am using access and I inserted the code from the first post and I could not get it to work. I am new at this. Could you give me a little more infoon this line of cade and where I insert it into my form.

Thanks for your help,
Danny.

 

[onpnt]

if you are refereing to my code it was all suedoecode that needed to be properly adjusted to your needs.

mwolf probably understood your question on duplicate records better then i did though. unless that is what you are really trying to check for is last name and first name equalities

what part did you need help with
the count of records? to limit the amoutn of entries ---------------------------------------
{ str = "sleep is good for you. sleep gives you the energy you need to function";
ptr = /sleep/gi;Nstr = str.replace(ptr,"coffee"alert(Nstr); }

http://www.onpntwebdesigns.com

 

[Tarwn]

Ok, this will depend largely on how you have set up the site, but:
There are two ways to handle this, either pre-query or post-query.
Pre-query:
Basically this will mean that you are handling full classes before the student has a chance to click on it. You will a check after they attempt to view a class in case they sat for a while and omsone stole the last seat, so you may want to stick with the post query.
Basically when you list the classes they can choose from, you could also list either the number of open seats (count the records from the regitration table for a specific class, subtract from the number of allowed seats) or list the number of allowed seats (which I assume is in your class table) and the number of seats already registered (again, this would be a count on all records in the Registration table that have the same id as the class in the class table).
This will allow you to prinlt links for classes that have open seats and no links for classes that are closed. Greatly reducing the chance that a class gets overbooked. The problem however is what happens when two people both sign up for a class with 39 of 40 seats filled?

Post-Query:
After the student selects a class you want to check if there are any openings in that class. The easiest way to do this will be how onpnt has demosntrated above. First do an SQL query against your database to select the count of records in the registration table that match the given class. Assuming the maximum number of seats is actually kept in your class table, you will want to cross the tables like this:

SELECT Class.class_id, Class.class_name, Class.max_seats, Count(Registration.registration_id) as num_seats_closed FROM Registration, Class WHERE Class.class_id = Registration.class_id

This SQL statement makes some assumptions concerning your table structure, but they should be fairly obvious field name assumptions.
Next you will want to check the recordset you execute this query into. Convert the "max_seats" and "num_seats_closed" field values into integers (using cInt) and make sure "num_seats_closed" is less than "max_seats". After doing this you can either redirect based on the result (which I don't advise) or simple use the data you have already retrieved to either a) insert the student into the registration table with the selected class_id (if there is an opening) or b) Show an error page to tell them the class is full.
This could work something like:

If cInt(rs("max_seats")) > cInt(rs("num_seats_closed")) Then
   'do your AddNew or SQL Insert Statement here
   Response.Write "You have been added to the role for registration in class " & rs("class_name")
Else
   'number of seats is already over or equal to max
   Response.Write "This class has already been filled, please return to the previous page and select another class."
End If

Hope this helps you in the direction your looking for,
-Tarwn
[/code] --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
No more vacation for me

FAQ FAQ20-2863
= new Forums.Posting.General.GettingAnswers()

 

[mwolf00]

WB Tarwyn -----------------------------------------------------------------
"Whether you think that you can, or that you can't, you are usually right."
- Henry Ford (1863-1947)

mikewolf@tst-us.com