Query or Macro for this function on a form? 2

[BLSguy]

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.

 

[dhookom]

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

 

[BLSguy]

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?

 

[dhookom]

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

 

[BLSguy]

Brilliance! Thank you very much for your help!

 

[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?

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.

 

[dhookom]

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?

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.

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.

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

 

[BLSguy]

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!

 

[dhookom]

I wouldn't do any development until class registration was removed from tblStudents and added to a separate table:
[pre]
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[/pre]


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

 

[Andrzejek]

I've also noticed in your [tt]tblCPR_Date[/tt] table you have fields:[tt]
Class_Date
Class_Month
Class_Time[/tt]
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.

 

[BLSguy]

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!

 

[Andrzejek]

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:

[pre]
Debug.Print Year(Date) [green]'2017[/green]
Debug.Print Month(Date) [green]'3[/green]
Debug.Print MonthName(Month(Date)) [green]'March[/green]
Debug.Print Day(Date) [green]'1[/green]
Debug.Print Weekday(Date) [green]'4[/green]
Debug.Print WeekdayName(Weekday(Date)) [green]'Wednesday[/green][/pre]

You are going to get the same outcome if you replace Date with 42795.5
[tt]
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))
[/tt]

You can substitute [tt]Date[/tt] 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.

 

[dhookom]

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:

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

 

[BLSguy]

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.

 

[dhookom]

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

 

[MajP]

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.