calculation in table

[steve carpineta]

I run a database which holds records for hot air balloon
flights at a recreational hot air ballooning event. I
have tables for Passengers, Pilots, and Flights. One
field of the "Flights" table is "seating capacity.
Balloons hold between 1 - 6 people. I have created a
form wherein there is a subform to list passengers for
each flight. You move through flights on a form viewing
flight information such as pilot and flight time on the
form, and you view the passengers for each flight via the
passenger subform in table view. I have a text box which
carries out a function which looks at the seating
capacity for the flight and subtracts the count of
passengers from it, leaving me with the difference, and
therefore the amount of seats left available on the
flight.

This form is great for looking at flights and determining
how many seats are open. For instance, if I need to find
a spot with three open seats, I can go through the
records until I come across a flight that matches my
needs.

However, I need to be able to query all my flights and
have it return flights which are NOT full, as the bulk of
my passenger additions will be only one person, and
scrolling through takes too long. The problem with this
idea is there is no field in my table to handle "Open
seats," as I rely on an unbound form calculation to
generate this number. Is there a way to get the results
of this function/calculation (seat capacity MINUS count
of passengers for the flight [full seats]) into the table
itself, so that I may run a query to return flights WHERE
SeatsOpen>0 ? I have tried putting calculations in a
field I created in my flight table, but nothing has
worked so far.

I would greatly appreciate any help or ideas. Thanks a
lot,

Steve

 

[Rob]

-----Original Message-----
I run a database which holds records for hot air balloon
flights at a recreational hot air ballooning event. I
have tables for Passengers, Pilots, and Flights. One
field of the "Flights" table is "seating capacity.
Balloons hold between 1 - 6 people. I have created a
form wherein there is a subform to list passengers for
each flight. You move through flights on a form viewing
flight information such as pilot and flight time on the
form, and you view the passengers for each flight via the
passenger subform in table view. I have a text box which
carries out a function which looks at the seating
capacity for the flight and subtracts the count of
passengers from it, leaving me with the difference, and
therefore the amount of seats left available on the
flight.

This form is great for looking at flights and determining
how many seats are open. For instance, if I need to find
a spot with three open seats, I can go through the
records until I come across a flight that matches my
needs.

However, I need to be able to query all my flights and
have it return flights which are NOT full, as the bulk of
my passenger additions will be only one person, and
scrolling through takes too long. The problem with this
idea is there is no field in my table to handle "Open
seats," as I rely on an unbound form calculation to
generate this number. Is there a way to get the results
of this function/calculation (seat capacity MINUS count
of passengers for the flight [full seats]) into the table
itself, so that I may run a query to return flights WHERE
SeatsOpen>0 ? I have tried putting calculations in a
field I created in my flight table, but nothing has
worked so far.

I would greatly appreciate any help or ideas. Thanks a
lot,

Steve

Steve,

Since the number of available passengers is dependant upon
the number of records in another table, it is better to do
the calculations on the fly and not store the number. If
something goes wrong with the update process, you could
end up with one table saying you have more capacity than
the actual list of passengers says you have.

What I would do here is create a query that will return
the flight information and calculate the remaining
capacity (by taking the capacity you store in the flight
table and subtracking the count of passengers already
booked for the flight). You would probably want to set
it up to ignore flights that have already occured and
flights that are already full.

Then I would set up a form with a listbox on it that
displays the results of the query. When a record in the
list box is clicked, you could have your existing form
open with the selected flight showing.

When you had an inquiry about booking a flight, open up
the new "List of Available Flights" form, scroll through
the list until you find an acceptable flight, and then
click on it to open the edit form.

The wizards will do most of the grunt work for you, after
you have the query set up to calculate the remaining
capacity. Without knowing your table structures, I can't
give you the SQL for what that should look like.

HTH

Rob