calculation in a table

[steve c]

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

 

[Dale Fye]

Steve,

You don't indicate how your database is setup, so I'll assume that in
addition to the flights table, you have a Balloons table which lists your
balloons and their capacity and other pertinent information, and a
FlightPassengers table which contains a FlightID and a PassengerID or
Passenger Name.

What you need to do is create a new query, include all three of these tables
in the query. Join Flights to Balloons on the BalloonID field, and join
Flights to FlightPassengers on the Flight ID. Now, place Flights.FlightID,
Flights.FlightDate, Flights.Pilot, Balloons.Capacity, and
FlightPassengers.PassengerID into the query grid. Click on the Sigma
(Totals) button to make this a aggregate query. In the Totals Row of the
query grid, set all of the fields to GroupBy except the PassengerID, set
this one to count. In the criteria row of the PassengerID field enter:

< Balloons.Capacity

If you have this table structure, this should give you all the flights where
the number of passengers is less than the capacity of the balloon.

HTH
Dale

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