Trying to Calculate Fields

[Robjr17]

I am trying to create a course registration website and I need to show how many
seats remain in a course. I am using Access 2000 and DWMX2004(ASP VBScript).
Here are the tables I have setup up(Based the Event Management Project in
Access): EventsTbl- EventID, AN EventName, TXT Status, TXT AvailableSpaces,
Num RegistrationTbl- RegistrationID, AN AttendeeID, Num EventID, Num RegDate,
Date AttendeesTbl- AttendeeID, AN AttendeeName, TXT DepartmentName, TXT I am
not sure how to go about setting up a recordset to subtract the number of
attendees from the number of available spaces. Thank you in advance for you
help.

[Ba Work]

Query the event table for the total number of seats available. Query
the registration table and count the number of regID's for the event in
question.

Subtract the number of regID's from the available seats and display the
results.

I think it will be able to create two seperate recordsets and then code
the calculation manually.

Brett


Robjr17 wrote:

> I am trying to create a course registration website and I need to show how many
> seats remain in a course. I am using Access 2000 and DWMX2004(ASP VBScript).
> Here are the tables I have setup up(Based the Event Management Project in
> Access): EventsTbl- EventID, AN EventName, TXT Status, TXT AvailableSpaces,
> Num RegistrationTbl- RegistrationID, AN AttendeeID, Num EventID, Num RegDate,
> Date AttendeesTbl- AttendeeID, AN AttendeeName, TXT DepartmentName, TXT I am
> not sure how to go about setting up a recordset to subtract the number of
> attendees from the number of available spaces. Thank you in advance for you
> help.
>

[bthouin]

I'd do the following:
- first add a field to your EventsTbl, called TotalSeats or something
like that, and which holds the number of available seats for the event
- then do, for a given EventId where you want to compute the number of
free seats:
SELECT COUNT(*) AS NoOfAttendees FROM RegistrationTbl WHERE EventId =
eventTobeTracked
- save the result (NoOfAttendees) in a variable, then do:
UPDATE EventsTbl SET AvailableSpaces = (TotalSeats - NoOfAttendees)
WHERE EventID = eventTobeTracked

But of course, the more clever method would be to subtract one
AvailableSpaces from the corresponding row in the EventsTbl every time
somebody register for an event, and check at the same time that
Available Spaces is > 0...

Robjr17 wrote:

> I am trying to create a course registration website and I need to show how many
> seats remain in a course. I am using Access 2000 and DWMX2004(ASP VBScript).
> Here are the tables I have setup up(Based the Event Management Project in
> Access): EventsTbl- EventID, AN EventName, TXT Status, TXT AvailableSpaces,
> Num RegistrationTbl- RegistrationID, AN AttendeeID, Num EventID, Num RegDate,
> Date AttendeesTbl- AttendeeID, AN AttendeeName, TXT DepartmentName, TXT I am
> not sure how to go about setting up a recordset to subtract the number of
> attendees from the number of available spaces. Thank you in advance for you
> help.
>