expressions and relationships in an Access database

[zavala]

Hey all,
This question came in through email.
--------------------------------------------------
I have an Access database that is used to keep track of Alumni trips and passengers going on trips.
It is an old database with several complicated expressions and relationships.
I think the problem lies in the trip count expression in the query.
Here is the expression that is used to count travelers for each trip.
expTravCount: Sum(IIf(([SpouseNotTravelWith]=True Or [SpouseLastName] Is Null Or [Travnottravelwith]=True),1,2)) I have created a query that shows all the trips that are taken between July 1, 2006 and June 30, 2007 (fiscal year).
When I created the query it shows the trips that have passengers already signed up, but will not show the trips that do not have passengers yet.
The problem with this expression is that it will not show the trips that do not have any passengers yet.
Can you help? Do you know of someone who can help?
---------------------------------------------------------------

Thanks
Catherine
--

[emurphy1]

Here is the expression that is used to count travelers for each trip.
expTravCount: Sum(IIf(([SpouseNotTravelWith]=True Or [SpouseLastName] Is Null Or [Travnottravelwith]=True),1,2))

Translated to English the query says something like this, "If the "spouse not traveling with" flag is true, or ,the spouse's last name is empty, or the "not travel with flag" is true, then count = count +1, otherwise count = count + 2.


When I created the query it shows the trips that have passengers already signed up, but will not show the trips that do not have passengers yet.
The problem with this expression is that it will not show the trips that do not have any passengers yet.
Can you help? Do you know of someone who can help?

The query needs to be changed, perhaps by adding another OR clause. I can't tell you exactly how to form the new query with out knowing the table structure in your database. The goal is to figure out what, in the tables, indicates a trip with no passengers and add that to the query. For example, you may have a column in a table called NumPassengers that holds the number of passengers signed up for a trip. If there are no passengers signed up it could be that NumPassengers is NULL. Thus you would add OR NumPassengers is NULL to your query.