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

Query or Macro for this function on a form?

Query or Macro for this function on a form?

(OP)
Hello everyone!

I am trying to set up a scheduling system.

I have two tables with the following (pertinent to this question) fields:

tblStudents:
StudentID (PK)
ClassID (FK)

and

tblCPR_Date:
ClassID (PK)
Class_Date
Class_Month
Class_Type
Class_Time

I'm trying to generate a query that will do the following: Allow the user to select from combo box list on frmCPR_Date, tblCPR_Date!Class_Month that will open a table on frmCPR_Date displaying the following fields and information:

tblCPR_Date!ClassID (doesn't need to show)
tblCPR_Date!Class_Date
tblCPR_Date!Class_Month
tblCPR_Date!Class_Type
tblCPR_Date!Class_Time
The sum of StudentIDs associated with each ClassID, i.e. the number of students registered to each class date - preferably with a way to set the maximum to 12.

Any solutions, suggestions, or guidance would be sincerely appreciated! Thank you for your time.

RE: Query or Macro for this function on a form?

First, build a totals query including both tables. Join the ClassID fields making sure you are selecting all the records from the tblCPR_Date table. Group by all of the "displaying the following fields" and count the number of StudentIDs.

Use this as the record source of a subform which will use the Link Master/Child on the combo box and the Class_Month field.

You don't say anything about registering so I can't answer that part of your post.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

RE: Query or Macro for this function on a form?

(OP)
Wow!! Thank you so much dhookom!! I was seriously struggling. As far as registering, I'm trying to find a way to limit the count to no more than 12. I'm not sure how to go about that. My guess is using a validation rule. But can I use a validation rule in a query to prevent adding more than 12 records according to the output of a query?

RE: Query or Macro for this function on a form?

You should always use forms for data entry. If you have a dropdown of classes, just keep the query I mentioned with a column for the number of students. Use the after update of the combo box to pop up a message if the column is 12 or greater.

BTW: your class table should have a column for the max number of students. Don't ever hard-code numbers like this. Always use data.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

RE: Query or Macro for this function on a form?

(OP)
Brilliance! Thank you very much for your help!

RE: Query or Macro for this function on a form?

(OP)
I added a Class_Max field per your suggestion (great idea, thanks!) but now I have run up against a new set of challenges. How do I get the query to display ClassID's for which there are no associated StudentID's?

I'm still researching to try and figure out how to make the combo box month selection cause the subreport to open with the totals query only displaying courses for the selected month.

Finally, I generated a report to display the query on the form, but I don't know how to make a given date selectable to open a new form.

RE: Query or Macro for this function on a form?

Quote (BLSguy)

I added a Class_Max field per your suggestion (great idea, thanks!) but now I have run up against a new set of challenges. How do I get the query to display ClassID's for which there are no associated StudentID's?

Quote (dhookom)

Join the ClassID fields making sure you are selecting all the records from the tblCPR_Date table.
Double-click the join and select the proper option.

Quote (GLSguy)

I'm still researching to try and figure out how to make the combo box month selection cause the subreport to open with the totals query only displaying courses for the selected month.

You need to decide if you are opening a table, subreport, form, or subform. I hope it's a subform.

Quote (GLSguy)

Finally, I generated a report to display the query on the form, but I don't know how to make a given date selectable to open a new form.

I would add a double-click event to the date control to open a form filtered to the correct date.

I'm not sure you have provided the big picture of what you want/expect to do with your form(s).

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

RE: Query or Macro for this function on a form?

(OP)
Ah! I found the join option you were referring to and it worked perfectly! If you say I need a subform, that's what I'll go with. I'll do some research as I'm not sure how to get the data results from the query to appear on my form according to the selected month.

I'm also not seeing how I would use an individual row on a table as a control to add a double-click event to it.

I'd be happy to provide a big picture idea! First, my DB as it's currently set up:


Here is what I'd like my form setup to be like:


The overall goal for this particular project is to have a DB that is easy for an untrained admin to add classes based on my own "how to" write up. Also to provide an easy UI for users to view available class dates and remaining seats, then select a class and input a new students data and reserve there spot with a click. Lastly to be able to have this happen concurrently with multiple users adding records simultaneously if need be.

With the aforementioned accomplished, I'd like to set up a simple system to send out e-mails and print a class attendance roster as needed. As I learn more about this software, I hope to maximize the efficiency of a lot of the things going on in my office.

As always, your help has been sincerely appreciated! Thanks again for helping a newbie grasp this software!

RE: Query or Macro for this function on a form?

I wouldn't do any development until class registration was removed from tblStudents and added to a separate table:
tblRegistration
==============================================
RegID          autonumber primary key
regStudentID   link to tblStudents.StudentID
regClassID     link to tblCPR_Date.ClassID
regStatus      possible value for enrolled, waiting list, dropped, etc
regModifyDate  date of last status change
regModifyBy    who made the modification
regComments    possible comments for instructor or staff 

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

RE: Query or Macro for this function on a form?

I've also noticed in your tblCPR_Date table you have fields:
Class_Date
Class_Month
Class_Time

Do you need all 3? You can set up just one field (Date/Time) and keep all that info in just one field.
You can always retrieve just date, or just month, or just time from it. And you will avoid the possibility of having 1/1/2018 in Class_Date and June in Class_Month...

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Query or Macro for this function on a form?

(OP)
Dhookom and Andrzejek, thank you both! I can see the wisdom in each of your suggestions. But I have a couple questions.

First, dhookom, I've added tblReg as you specified. Does that mean I should remove the ClassID field from tblStudents? I can make those changes easily enough at this point, I think.

Andrzejek, I currently have all my forms and queries set up with the separate fields because I thought that would be the best way to do it. For instance, I have two combo boxes on frmEmail, when you select the month, the next combo box populates with the available course dates for that month. I'm not sure how I could maintain that feature with those fields combined.

Thank you!

RE: Query or Macro for this function on a form?

Without going deeper into your approach....
A Date/Time field in any programing is just a number. Today, March 1, 2017 at 12 noon is 42795.5 which means 42795.5 days passed since Jan. 1 1900
How you display it is up to you. Access shows you 3/1/2017 12:00 PM, but you can retrieve another 'parts' of it:

Debug.Print Year(Date)               '2017
Debug.Print Month(Date)              '3
Debug.Print MonthName(Month(Date))   'March
Debug.Print Day(Date)                '1
Debug.Print Weekday(Date)            '4
Debug.Print WeekdayName(Weekday(Date)) 'Wednesday 

You are going to get the same outcome if you replace Date with 42795.5

Debug.Print Year(42795.5)
Debug.Print Month(42795.5)
Debug.Print MonthName(Month(42795.5))
Debug.Print Day(42795.5)
Debug.Print Weekday(42795.5)
Debug.Print WeekdayName(Weekday(42795.5))


You can substitute Date with the date field from your data base and this will work the same.

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Query or Macro for this function on a form?

I would stop using the ClassID in tblStudents

To populate tblRegistarion, you can use SQL like the following. You can also add more field if you would like:

CODE --> query

INSERT INTO tblRegistration (regStudentID, regClassID, regStatus)
SELECT StudentID, ClassID, "E"
FROM tblStudents
WHERE ClassID is Not Null 

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

RE: Query or Macro for this function on a form?

(OP)
So I've made all the changes suggested thus far and cleaned up tables, names, etc. The current issue I'm working on is trying to restore the functionality of my application but using the improved techniques suggested here. So here's my current database:


And here's frmReg:


I need the combo box indicated (the only one on the form now that I think of it) to display the long dates (e.g. Thursday, March 09, 2017) from tblClasses, and on click of Complete Registration, add a new record to tblStudents and tblReg that are tied to ClassID from tblClasses. I've tried every approach I can think of to accomplish this and have thus far failed to accomplish my goal.

dhookom, thank you for an excellent query design for populating tblReg, but presently my tblStudents is empty. I will be manually adding all current registrations using my forms once I've successfully got the rest of them working as planned.

RE: Query or Macro for this function on a form?

I would make place the Class Date in a continuous subform with tblReg as its record source. Registration comments needs to be added to the subform. Set the Link Master/Child of the subform control to the studentID fields. You will need to save the student record in order to connect to tblReg.

The combo box for the Class Date should be limited to only future, open classes.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

RE: Query or Macro for this function on a form?

Quote:

I need the combo box indicated (the only one on the form now that I think of it) to display the long dates (e.g. Thursday, March 09, 2017) from tblClasses, and on click of Complete Registration, add a new record to tblStudents and tblReg that are tied to ClassID from tblClasses. I've tried every approach I can think of to accomplish this and have thus far failed to accomplish my goal.
You are making it too hard. Seperate the forms for adding a new student from registering a student. To add a new student simply bind the form to the student table. If you want to do it in one form then the registration portion needs to be a subform. I posted in the other thread a potential design. If you wanted to use the above form then get rid of the class date combobx. Add a subform containing a query that joins table reg and table classes. Link the subform by student ID. In the subform you will then have a combobox bound to regclassid. To allow you to add classes to that student.

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