INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Put together some forms with controls and queries

Put together some forms with controls and queries

(OP)
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!

RE: Put together some forms with controls and queries

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.

RE: Put together some forms with controls and queries

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Put together some forms with controls and queries

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.

RE: Put together some forms with controls and queries

(OP)
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!

RE: Put together some forms with controls and queries

(OP)
The image I meant to attach:

RE: Put together some forms with controls and queries

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Put together some forms with controls and queries

(OP)
Thank you.

RE: Put together some forms with controls and queries

It is possible that a student...

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

Stuff happens.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Put together some forms with controls and queries

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close