count zero values in a query

[steve carpineta]

I have tables set up to handle relationships for hot air
balloon flights. I have a query which searches for "non-
full" flights by counting the passengers in each flight,
then comparing that count with a field in the flights
table for that flight called "capacity," an integer which
tells us how many people the balloon can seat.

So for instance, if flight 1000's capacity is 6, and it
only has four passengers seated, the query includes this
record because it is "non full." THe formula is
basically the following in the criteria line of
the "COUNT OF PASSENGERS" count result: <[Flights].
[Capacity]

The query works great, except for that flight records
where there are no passengers are not included. I do not
understand whether the result of the count of passengers
is 0, null, or whatnot in cases where there are no
passengers. It would seem that the formula would still
work. I tried adding zero-length strings and isNull to
the criteria so that in case the count was returning
these values, they'd be included, but to no avail. In
fact, a zero length string gave me a data type mismatch
error and thus i couldn't try it.

I'd appreciate any help that anyone can give me, and
thanks for reading!

-Steve

 

[steve again]

New development:

I tried pressing the space bar in the "passenger name"
field of a flight with no passengers, thereby creating an
autonumber in my passengers table and establishing that
record (The record of passenger_name " ".) When I do
this, the flight is included in the query, showing the
count of passengers as 0 as it should. Do I have to go
through and hit space bar on each record in my database?
HELP! Thanks a lot, once again,

Steve C

 

[B-SQL]

First I have to warn you that I am so new as to be
dangerous. Secondly, I am very very familiar with SQL in
a DB2 environment and I am struggling to change my ways.
I am trying to learn by replicating problems I see here
and then trying to fix them.

With the disclaimers out of the way, here is what I tried
to do. I created three tables: flights, riders, bookings.
If I query the bookings as you have, I too get nothing for
the flights with no riders. In DB2 I can do a subselect
for rows where "not exist" but I'm not sure how to do it
in Access.

Solution. Within your query "create the relationship" as
left join so that it grabs all the rows for the balloons
regardless of riders. Then you can create your query to
look for counts of riders less than capacity or do the
math in the query and simply report the excess capacity.
At the risk of being annoying, right click on the link
between tables in the query, select join properties and
then option 2 or 3 (depending on how you set up your
tables, the explanation should be clear)


hope that helps you, I know it helped me. Thanks!

-----Original Message-----
New development:

I tried pressing the space bar in the "passenger name"
field of a flight with no passengers, thereby creating an
autonumber in my passengers table and establishing that
record (The record of passenger_name " ".) When I do
this, the flight is included in the query, showing the
count of passengers as 0 as it should. Do I have to go
through and hit space bar on each record in my database?
HELP! Thanks a lot, once again,

Steve C

-----Original Message-----
I have tables set up to handle relationships for hot air
balloon flights. I have a query which searches for "non-
full" flights by counting the passengers in each flight,
then comparing that count with a field in the flights
table for that flight called "capacity," an integer which
tells us how many people the balloon can seat.

So for instance, if flight 1000's capacity is 6, and it
only has four passengers seated, the query includes this
record because it is "non full." THe formula is
basically the following in the criteria line of
the "COUNT OF PASSENGERS" count result: <[Flights].
[Capacity]

The query works great, except for that flight records
where there are no passengers are not included. I do not
understand whether the result of the count of passengers
is 0, null, or whatnot in cases where there are no
passengers. It would seem that the formula would still
work. I tried adding zero-length strings and isNull to
the criteria so that in case the count was returning
these values, they'd be included, but to no avail. In
fact, a zero length string gave me a data type mismatch
error and thus i couldn't try it.

I'd appreciate any help that anyone can give me, and
thanks for reading!

-Steve
.

.