count "0" cells

[steve carpineta]

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

 

[Jeff Boyce]

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.

 

[steve]

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

 

[Duane Hookom]

The advice you suggest you got from an MVP was probably not an Access MVP. I
know most of them and expect the multiple table advice came from an Excel
(or FoxPro ;-) MVP. Cindy W. did I get your attention?

I didn't notice a requirement regarding seat assignments so I assume you
don't need to know who is in which seat. I would create two tables
(minimum):
tblFlights
===========
fliFliID autonumber Primary Key
fliDeptFrom
fliArriveAt
fliSeatsAvail number to store how many seats may be available
fli...more fields

tblPassengers
================
pasPasID autonumber Primary Key
pasFliID foreign key to tblFlights.fliFliID
pasFirstName
pasLastName
pasSeatAssigned (optional)
pas...more fields

You will easily be able create a totals query based on the two tables that
counts the number of passengers currently booked on any flight and compare
it to the number of available seats.

 

[Jeff Boyce]

Steve

I might be reading more into your response than I need to, so help me stay
on track...

One of the notions of relational design is that you don't want to use
repeating fields. While this is the only way to handle the situation you
describe when using a spreadsheet, a relational database allows you to
re-structure your data.

The example I gave may not make sense in YOUR situation, but my impression
of a flight is that it can hold zero-to-many passengers. To build this
structure in Access, I would create a table with at least two columns:

trelBookings
FlightID (a foreign key from the tblFlights, which lists info ONLY
about flights)
PassengerID (a foreign key from a tblPerson, which lists info ONLY
about persons)

I would use one row per passenger-booked-on-flight. A flight with 10
passengers would have 10 rows. A flight with 2 passengers would have 2
rows. And a flight with no passengers would have ... NO rows!

If a given flight (from tblFlight) can only seat 20 passengers, that
information belongs in the tblFlight (info about a flight). If I want to
see if I'm "full" on that flight, I use a form to display the flight info, a
subform to list the passengers (or even a listbox), and a control on the
form that compares the count of passengers from the trelBookings table (for
that flightID) with the MaxPassengersAllowed information from the tblFlight
table.

While this is not the only way Access could be used, it represents, I think
a common practice, employing more of the relational strengths and avoiding
the costly (in time and effort) approach of treating Access like a
spreadsheet.

And as for your final thought:

My sincerest thanks to those willing to read carefully
and respond

I'll remind you that I did, and that I responded to your final thought in
your original post, i.e.,