Referencing Totals from One Table in the Fields of Another

[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
CPR_DateID
First_Name

and

tblCPR_Date
Class_Date
Class_Month
Seats_Left

Each Class_Month has several Class_Dates. Each Class_Date has 12 open seats. I need a way to take the number of fields with the same CPR_DateID from tblStudents and subtract that from 12 in Seats_Left from tblCPR_Date. I also need to set a validation rule to ensure that no CPR_DateID is associated with more than 12 students.


Perhaps I may be approaching this wrong, so here is my larger picture goal:

I have a form that starts with only the combo box "Select_Month" upon the selection of which I would like to open a table below said combo box that displays all of the Class_Date fields associated with only the selected Class_Month as well the Seats_Left. Ideally, if a user double clicks on a row showing zero seats remaining, a pop up exclaims "Class full!", whereas clicking on a row with open seats takes them to another form with the date they selected populating a combo box on that form.

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

 

[Andrzejek]

Looks to me that the number of seats available in any particular class is a calculated field and should not be kept in any table in the data base. You may want to user a query instead.
You may find that there are 7 records with people assigned to a class, but your other field shows 9 people in the class. Well, that's wrong.
I would use a Constant in my program set to 12 and calculate either 'on-the-fly' or use the query of how many students assigned to a Class and if that calculation is less or equal to my Constant.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[BLSguy]

Hi Andrzejek, thank you for the response. I did some research on use of a Constant, but that seems to venture into VBA territory which I'm trying to steer clear of for this project. However, I do see some potential in the use of queries on my forms. So, for that, I shall venture over to the Queries section of this delightful forum. Thank you for your time.