Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Referencing Totals from One Table in the Fields of Another

Status
Not open for further replies.

BLSguy

Instructor
Feb 9, 2017
39
0
0
US
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.
 
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.
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top