Student count for Class Registration
in [Tables]
|
From: Dennis on 2 Mar 2010 21:45 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
From: Dennis on 2 Mar 2010 22:28 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
From: Tom van Stiphout on 2 Mar 2010 23:24 On Tue, 2 Mar 2010 19:28:01 -0800, Dennis <Dennis(a)discussions.microsoft.com> wrote: You could use your new Count query before you save the data. Perhaps in your Form_BeforeUpdate you could write: if DLookup("CountOfCustAcctNo", "tblClass_Count", "ClassNo=" & Me.ClassNo) > MAX_VALUE then Msgbox "Yo, no more!" Cancel = True 'This stops the record from being saved end if Btw, from a naming convention standpoint it's a bad idea to name a query "tbl...". -Tom. Microsoft Access MVP >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
From: Dennis on 3 Mar 2010 00:16 Tom, Thanks for the info. I will play with it. Opps, I mistyped my query name. Query name should have been qrytblClass_Count. My naming convension for better or worse is qry for a true query that answer a question. qrytbl ... for a "logical view" of a table or series of joined tables qryupd for an update query qryapp for an append query etc. Do you have a better suggestion for a name convension? Thanks, Dennis
From: Jeff Boyce on 3 Mar 2010 19:25
Pardon my intrusion ... You could search on-line for "MS Access" and "naming convention" to get additional ideas. I figure that the only Access object that starts with a "q" is a query, so I use "qlkp", "qapp", etc.... (JOPO - just one person's opinion) Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Dennis" <Dennis(a)discussions.microsoft.com> wrote in message news:93DA9841-574F-422B-99C0-9D423180E911(a)microsoft.com... > > Tom, > > Thanks for the info. I will play with it. > > Opps, I mistyped my query name. Query name should have been > qrytblClass_Count. My naming convension for better or worse is > > qry for a true query that answer a question. > qrytbl ... for a "logical view" of a table or series of joined tables > qryupd for an update query > qryapp for an append query > > etc. > > Do you have a better suggestion for a name convension? > > Thanks, > > Dennis > > |