If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
calculation in a table
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 SeatsOpen0 ? 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 |
#2
|
|||
|
|||
calculation in a table
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 "steve c" wrote in 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 SeatsOpen0 ? 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 |
Thread Tools | |
Display Modes | |
|
|