Create count using different tables in access

Question

I have a sessions table with a field called capacity and a scheduling table with a session field. When a session is scheduled I need to be able to subtract one from the capacity in the sessions table and keep it in a field called available (10 of 20) in the sessions table. When the session capacity is reached (20 of 20), I would like the field to turn red so it is known the session is full. Has anyone done this before and how would I go about it?

Answer 1

I wouldn't use table calculated fields AT ALL; they're very limited and not necessary. I also would not expose your Table datasheets to view; they should be "under the hood", and all your interaction with the data should be done using a Form. The calculation can be done in a Query used as the Form's Recordsource, or with expressions like =DCount("*", ...) in the Control Source of a textbox on the Form.

Answer 2

Typically you don't want or need to store the available value since it can be calculated on the fly. You can use DCount()

=DCount("*","Sessions","[Session ID] = " & [Session ID]) & " of " & [Session Capacity]

This assumes there is a numeric [Session ID] field in both tables.

Answer 3

What type of field do I use? The expression DCOUNT cannot be used in a calculated field.

Answer 4

Will this work with the user choosing the session id from a drop down in the scheduling table?

Answer 5

I found how this is done in Excel:

Total Scheduled = IF(Reservation date=",",COUNTIF(Scheduling!Session ID:Session ID,Session number)& " of " & Session Capacity)

Does this help?