A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Student count for Class Registration



 
 
Thread Tools Display Modes
  #1  
Old March 3rd, 2010, 03:45 AM posted to microsoft.public.access.formscoding,microsoft.public.access.tablesdbdesign
Dennis
external usenet poster
 
Posts: 1,222
Default Student count for Class Registration

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 structu
-----------------------

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
  #2  
Old March 3rd, 2010, 04:28 AM posted to microsoft.public.access.formscoding,microsoft.public.access.tablesdbdesign
Dennis
external usenet poster
 
Posts: 1,222
Default Student count for Class Registration

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
  #3  
Old March 3rd, 2010, 05:24 AM posted to microsoft.public.access.tablesdbdesign
Tom van Stiphout[_2_]
external usenet poster
 
Posts: 1,653
Default Student count for Class Registration

On Tue, 2 Mar 2010 19:28:01 -0800, Dennis
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

  #4  
Old March 3rd, 2010, 06:16 AM posted to microsoft.public.access.tablesdbdesign
Dennis
external usenet poster
 
Posts: 1,222
Default Student count for Class Registration


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


  #5  
Old March 4th, 2010, 01:25 AM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Student count for Class Registration

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" wrote in message
...

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




  #6  
Old March 4th, 2010, 03:41 AM posted to microsoft.public.access.tablesdbdesign
Tom van Stiphout[_2_]
external usenet poster
 
Posts: 1,653
Default Student count for Class Registration

On Tue, 2 Mar 2010 21:16:01 -0800, Dennis
wrote:

My take on this is in my company's programming standards. Essentially
it says:
Even if you don't follow any of the many guidelines in this document,
BE CONSISTENT.

I think consistency is much more important as what the actual prefix
(if any) might be.

-Tom.
Microsoft Access MVP



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

  #7  
Old March 4th, 2010, 05:50 AM posted to microsoft.public.access.tablesdbdesign
Dennis
external usenet poster
 
Posts: 1,222
Default Student count for Class Registration

Jeff,

Your comment: You could search on-line for "MS Access" and "naming
convention" to get additional ideas.


My Response: Opps, brain damage on my part. I should have known that, but
thank for the reminder. I will do that. Thanks for the reminder.


Your comment: 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)


Reponse: Ah, simpler and it answers the question.


Thanks.

Dennis


  #8  
Old March 4th, 2010, 05:53 AM posted to microsoft.public.access.tablesdbdesign
Dennis
external usenet poster
 
Posts: 1,222
Default Student count for Class Registration

Tom


Your comment: My take on this is in my company's programming standards.
Essentially it says: Even if you don't follow any of the many guidelines in
this document, BE CONSISTENT.

I think consistency is much more important as what the actual prefix (if
any) might be.


My response: Your are preaching to the choir. I *strongly* agree. In one
of my past jobs, I had to convert a small system written by one person. It
consisted of 40 programs. Each of the programs was very different from the
previous so each program was a new adventure in discovery. If they had been
consistent, it would have taken about 1/2 the time.

Thanks,

Dennis


-Tom.
Microsoft Access MVP



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

.

  #9  
Old March 5th, 2010, 03:53 AM posted to microsoft.public.access.tablesdbdesign
Tom van Stiphout[_2_]
external usenet poster
 
Posts: 1,653
Default Student count for Class Registration

On Wed, 3 Mar 2010 20:53:01 -0800, Dennis
wrote:

Yes, I have had similar experiences. Almost comical. One time a UI guy
would every day decide what type and color of buttons he would use
today. The resulting Christmas Tree was a firing offense imho.

With source code I always look for consistency. As the poor
maintenance programmer coming after our consistency-challenged
developer you initially have to assume that all these subtle
variations of a theme have a deeper meaning you have not yet
discovered, but which will break the code if you change them. Only
MUCH later you conclude the person was indeed challenged in this area
and his employer did not get value for the money.

-Tom.
Microsoft Access MVP


Tom


Your comment: My take on this is in my company's programming standards.
Essentially it says: Even if you don't follow any of the many guidelines in
this document, BE CONSISTENT.

I think consistency is much more important as what the actual prefix (if
any) might be.


My response: Your are preaching to the choir. I *strongly* agree. In one
of my past jobs, I had to convert a small system written by one person. It
consisted of 40 programs. Each of the programs was very different from the
previous so each program was a new adventure in discovery. If they had been
consistent, it would have taken about 1/2 the time.

Thanks,

Dennis


-Tom.
Microsoft Access MVP



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

.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:40 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.