Put together some forms with controls and queries

[BLSguy]

Hello everyone! I have no formal education in Access 2010, only a lot of googling. I'm trying to design a fairly simple scheduling database for 5-8 users, ideally with potential for expansion so it can be utilized to archive records and generate various reports.

Some points that may matter: Maximum 180 classes per year with 12 students each, so no more than 2160 students per year. DB needs to maintain three years of records. I need to be able to send e-mails to registered students, ideally with the date automatically placed in the e-mail based on table data.

Currently I've set up tables and relationships based on what I've read about good database design and avoiding redundant data and the like. As follows:

I'm trying to figure out how to put together some forms with controls and queries (perhaps macros, but VBA scares me for the time being) to do the following:
View seats taken/remaining in classes, ideally in a week or entire month view (perhaps with conditional formatting for easy ID of available classes)
Set up a validation rule to prevent booking more than 12 students to a class

And finally splitting the database for the users to access a network drive front end. I know it can be done, I'm just having difficulty gleaning what I need to know from the highly technical Microsoft documentation that covers these topics. I wouldn't ask anyone to give me the needed codes or anything, but a critique of my table setup and a point in the right direction would be sincerely appreciated!

 

[BLSguy]

Also, SkipVought asked that I reference my first attempt at attaining help from Tek-Tips. It can be viewed here: [URL unfurl="true"]http://www.tek-tips.com/viewthread.cfm?qid=1774363[/url], Thanks!

 

[Andrzejek]

Couple of points:
Even though Access does allow you to use spaces in the fields' names, I would not do it. Most of the time - in your picture - you do use an underscore (Last_Name) but sometimes you use a space [Sign Up Date]

It will help if you would present your process in details. You started it already here:

"Maximum 180 classes [offered? or a student can take up to 180 classes?] per year with 12 students each, so no more than 2160 students per year (irrelevant, calculated amount). DB needs to maintain three years of records [irrelevant, you keep all years and just report on the time span you choose]. I need to be able to send e-mails to registered students, ideally with the date automatically placed in the e-mail based on table data."

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

I do see several tables where you have Welcome/Reminder Sent. Is this a one time in the lifecycle of the employee? Same with Sign Up Date or Cancelled or any other field that seems to be associated with an Employee's specific class or any other multiple entity. If not, these need to be in another table related to classes, which I do not see in your ERD.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

Also, you have a table tblRank which 'ranks' people in tblStaff, tblStudent and tblSupervisors. Do you use the same rank for Staff, Students and Supervisors or Staff has their own ranks, Student have theirs and Supervisors have their ranks, but you keep all Ranks in one table?
The same goes for BLS_Exp (whatever that is)

Tables tblStaff, tblStudent and tblSupervisors keep a lot of the same data, even fields are named the same. It may be beneficial to have just one table (tblPeople or tblPersons) and differentiate which record is a staff member, or a student, or a supervisor. Again, it may be the way, it may not...

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[BLSguy]

Andrzejek, although not reflected in the attached image (Cell Phone was fixed after), I replaced all spaces with underscores.

SkipVought, Welcome/Reminder Sent will need to be reset somehow if the employee recertifies at this facility within two years. Cancelled is just intended to hold their information should they decide to sign up again later, but I could do away with that. Although I would have to permit record deletion on the front end to accomplish that. I think I get what you are saying though. However, with the frequency of updates needed (approximately two years apart, if ever), would it still behoove me to place things like sign_up_date on another table?

Andrzejek, There will not be more than 180 classes in a year, of which any could be registered for once in a two year span by any given student.

I'm trying to set up a query that will display available course dates by type (Basic Life Support or Heart Saver), along with how many students are registered for a given date (0-12).
From there, I'd like a user to be able to click a date, have that date be auto-populated in the following form which would allow the user to input the relevant data to register the student for that course.
At some point, I need to make reports by class day and means of e-mailing students to remind them of their upcoming classes.

Thanks again for all your help!

 

[BLSguy]

The image I meant to attach:

 

[SkipVought]

Someone in the future might want to know sign up date, cancel date, start date, completion date at least for any class.

Maybe you only have one class per employee per year now. But things change. I would advise against designing myopically.



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[BLSguy]

Thank you.

 

[SkipVought]

It is possible that a student...

signs up but never starts a class
signs up, starts class but never finishes

Stuff happens.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[BLSguy]

I understand and appreciate modifying my thread title, but I don't understand what was wrong with my last post that it should be reduced to "thank you"... Regardless, I'm new to this forum and have little to contribute, and so I apologize if I'm offending anyone for lack of familiarity with this community. I'm working my way through the project and will try to isolate my threads to more specific inquiries.

And SkipVought -
It is possible they sign up and don't show, for which we track no-shows. It's virtually unheard of that a student would sign up, show up, and not complete the class. I appreciate your help thus far, but I'm going to try other, more specific areas of this forum for help figuring this out.