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

Referencing Totals from One Table in the Fields of Another

Referencing Totals from One Table in the Fields of Another

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

RE: Referencing Totals from One Table in the Fields of Another

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.

RE: Referencing Totals from One Table in the Fields of Another

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

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