Calculating Fields and Auto Update (1 Viewer)

johansena

Registered User.
Local time
Today, 08:30
Joined
Jun 15, 2013
Messages
12
Ok, I have a Courses Table.

In this table I have two fields Seats Available and Seats Remaining

When I register a student to a Course in the Courses table I would like the corresponding Seats Remaining field for that course to decrease automatically.

I can, of course, run a report or a query to see how many are enrolled in the course and then manually update the Seats Remaining field (which is what I currently do), but I would like this process to be automated each time I register a new student.

Any suggestions?
 

Catalina

Registered User.
Local time
Today, 06:30
Joined
Feb 9, 2005
Messages
462
Your approach is not normalized.

You only need one field: Seats Available. When you need to know
how may seats are left you can count the students who signed up and
subtract that number from the total seats available.


The way you do it now is not practical. Suppose an enrolled student
cancels, then a seat becomes available again.

It is a good idea to read up on normalization. As a rule calculated values
are not stored but computerized on the fly, usually in a query.


Catalina
 

johansena

Registered User.
Local time
Today, 08:30
Joined
Jun 15, 2013
Messages
12
Hmmm....

My courses will not always have the same number of seats available. Each course is different which is why I have the two values.

But I see what you are saying. I am somewhat familiar with creating the calculated field in a query.

I would like to learn more about the practical application of making this work.
 

Catalina

Registered User.
Local time
Today, 06:30
Joined
Feb 9, 2005
Messages
462
Do a search on this subject, it has come up here numerous times.
 

SBDatabases

New member
Local time
Today, 13:30
Joined
May 2, 2011
Messages
2
What I'd be inclined to do is have separate Courses and Students tables.


tbl Courses
would have say Course ID, Course Name, Seats Available, Seats Remaining

tblStudents would have say Course ID, Student Name

Then I'd create a Course form with Students as subform linked by Course ID. You could then count students enrolled in the subform's footer and set Seats Remaining as Seats Available minus this count.

This would also allow you to track which courses any student had been on, for example.
 

Users who are viewing this thread

Top Bottom