do i need six tables, or one?

[steve matco]

First I wrote:

I am working on a database that seats passengers and
pilots on hot air balloon flights.

I have six tables for flights, one for flights with 1
passenger, 2 passengers, etc all the way to 6
passengers. One of the necessary functions of the
database is to seat passengers that will approach a check
in table on open flights. I have a system set up right
now that scans the fields "Passenger1"
through "Passenger6" in each flight record and determines
whether ANY of those fields are empty. If any of the
fields turn up empty, an update query turns the "Flight
Full?" check box to "NO" for that flight. However, if
all "Passenger1" - "Passenger6" fields are full, the
update query changes to "Flight Full?" check box
to "YES." This way when I check for Open flights, I do
indeed get flights with any sort of opening, but I have
no way to pinpoint exactly how many open seats i'm
looking for.

What I want to do from here is find a way to count how
many of the "Passenger" fields in each record are indeed
a null value. This would enable me to prompt the user
for how many empty seats they need to find in a flight.
This way, if they need three empty spots, this query
would scan the tables and determine which flights have
three openings.

Thanks in advance for reading down this far, please offer
me any possible ideas you might have. Thanks,

Steve

Steve

Your data structure is fine ... for a spreadsheet! You don't need to (and
don't want to) use repeating fields (Passenger1, Passenger2, ...) in a
relational database.

Instead, create a table that has Flight & Passenger as fields, then use a
query to count how many Passengers you have for any given Flight.

--
Good luck

Jeff Boyce
<Access MVP>

To which I reply:

Jeff,

The problem is, some flights seat two passengers, so I
need to have a flight table to accomodate two spots, and
the same case for a six person flight, right?

To know which passengers are seated in each "flight"
record, wouldn't I have to have fields under
each "flight" record to accomodate that data? I would
really appreciate it it someone who bothers to post would
spend more time helping more descriptively and less time
scoffing at my very limited database talents. I
understand that I don't know too much about "data
theory," but I am having trouble understanding
how "creating a table with 'passenger' and 'flight' as
fields" will allow me to count how many spots are open on
records for flights, each of which accomodate different
numbers of passengers...? I tried having a simple
database where it was as easy as "flight"
and "passengers," but since some flights hold 4, some 3,
some 6, etc, I was told by some other access mvp that I
had to have a different table for each seating capacity.
For instance "Flights4" is my table for four person
flights. "Flights6" is my table for six person flights.
I was to scan these tables to find out EXACTLY how
many "null"s appear throughout "passenger1" -
"passenger6" (for six person flights, "pass1" - "pass5"
for five, etc.)

Let's say we have a six person flight with four spots
blank. How could you even accomodate that without having
a "flight" record with the fields "pass1, pass2 - pass6"
to hold those passengers names (which are looked up from
the "passengers" table.)

My sincerest thanks to those willing to read carefully
and respond

-Steve



Anyone?

 

[Les]

Steve,
I'm no expert by any means, but I would recommend the
following:

Flight table: primary key on flightnumber
flightnumber
numberofseats

Passenger table: primary key on Flightnumber, PassengerName
Flightnumber
PassengerName

Query:Include both tables, joined on Flightnumber.
Include flightnumber, numberofseats, Passengername.
Create a new field flightfull:IIF(CountofPassengername =
numberofseats,"Full","Not Full")
Change it to totals query, group by flightnumber,
numberofseats, count Passengername, and set flightfull to
an expression.

Hope this helps.

 

[Rob]

Steve,

You could do this with six tables, but Jeff is correct
about that not being the best way to handle it. It causes
a whole lot more problems than it solves.

What I would recommend would be a single table to hold all
of the passengers. Basically it would need three fields:
an AutoID, FlightID, and PassengerName (and perhaps
contact info for the passenger).

You would need another table to hold FlightID and a
Capacity (and likely date and maybe other info about the
flight).

You could query the first table to count the number of
passengers already booked to each flight. This result set
could then be compared to the Capacity in the other table
to allow you to select an appropriate flight to book an
additional group of people.

This has its own issues (perhaps the primary one being it
would be possible to add more passengers than the flight
could accomodate, so you might want to run another query
that would check the data for cases where # booked exceeds
the Capacity just as a double-check), but would be a lot
easier to deal with down the road.

FWIW...

Rob

 

[Chris Large]

I would recommend a structure as follows:

tblFlights
----------
FlightID
FlightDate
FlightCapacity
....

tblPassengers
-------------
PassengerID
PassengerName
.....

tblPassengerList
----------------
FlightID
PassengerID


In this structure tblPassengerList is a junction table
between tblFlights and tblPassengers, contain just the
primary key fields from these tables.

With this structure you can easily find the number of
passengers on each flight with the following query:

SELECT FlightID, Count(*) AS SeatsFilled
FROM tblPassengerList
GROUP BY FlightID;

Save this query as qrySeatsFilled, then you can create the
following query to find the Seats Available:

SELECT tblFlights.FlightID, [FlightCapacity]-[SeatsFilled]
AS SeatsAvailable
FROM tblFlights INNER JOIN qrySeatsFilled ON
tblFlights.FlightID = qrySeatsFilled.FlightID;


hth

Chris