Exactly how do I use the Nz function in Access XP?

[Nikki Rosner]

Ok, I'm trying to create a report in Access XP from two queries. One
query tells me the ID, state and sum of the employees per state. The
other query tell me the same thing, except the sum of vehicles per
state. There are states where there are employees but no vehicles,
and of course, the report only spits out states where there's values
for both sums of employees and vehicles. I know about the Nz
function, but need to know how to properly use it. I need an
explenation a child could understand, since I've been searching Google
groups for hours and just can't seem to find MY answer. Ok, while in
the SQL Statement of my report, the columns are "ID", "State Name",
"Sum Of NO OF EMPLOYEES" and "Sum Of VEHICLE COUNT". When I go into
the SQL Statement of the report, exactly where do I type in the Nz
function and how do I word it? I tried: Nz([Sum Of VEHICLE COUNT], 0)
in the criteria line of the "Sum Of NO OF EMPLOYEES" column, but I
don't get the results I'm expecting. Please, someone... HELP!!!

 

[Jeff Boyce]

One approach would be to first create a query that returns all states (do
you have a states table?). Then create another query, using that first
query, joined to both the Employees and the Vehicles tables, using
"directional" joins. To do this, open a new query, select the first query
and the two tables, and drag "state" from query1 to each of the table's
"state" field.

Next, highlight one of the "join" lines and modify its properties to take
all of the query's rows, plus any matching rows. Do the same with the
second join line.

The concept is that you're telling Access to return every state (from the
first query), and the value from each of the other tables if any exists for
each state.

Note -- the disadvantage of having "state" in each table is they all have to
be spelled exactly the same! A more normalized design would have a state
table, and use the ID/code from that in each of the other tables.

 

[Nikki Rosner]

I did what you said, but still have problems:

One approach would be to first create a query that returns all states (do
you have a states table?).

............YES I HAVE THAT TABLE AND I DID THAT.

Then create another query, using that first query, joined to both the
Employees and the Vehicles tables, using "directional" joins. To do this,
open a new query, select the first query and the two tables, and
drag "state" from query1 to each of the table's "state" field.

............I DID THAT, TOO

Next, highlight one of the "join" lines and modify its properties to take
all of the query's rows, plus any matching rows. Do the same with the
second join line.

............I DID THAT, BUT IT KEEPS TELLING ME: THE SQL STATEMENT
COULD NOT BE EXECUTED BECAUSE IT CONTAINS AMBIGUOUS OUTER JOINS. I'VE
TRIED THAT KIND OF JOINING IN MANY DIFFERENT WAYS IN DIFFERENT QUERIES
AND REPORTS, AND ALWAYS GET THE SAME ERROR MESSAGE.

I've now been working (including research) for over a day now on this.
Everything I find online is either not valid for me, or I can't
understand what they're talking about. There has to be a simple
solution to this, but I can't find it. Thanks anyway.

Nikki Rosner

 

[Jeff Boyce]

Nikki

Please post the SQL statement.

 

[DIJ]

I've always just used
Nz([FIELDNAME])+0

This works fine for me and I use it almost daily.
DIJ
www.emu-x.com

 

[Nikki Rosner]

Yipee! I was able to figure it out and get it to work. The solution
was similar to what you said: the correct sequence of queries and
relationships. I kept getting the "ambiguous joins" error so instead
of trying my final query with more than two tables, I did another one
first with all but one of the tabels, took that query and added the
final table. I was then able to adjust the relationships without a
problem and with the help of the Nz function, viola! Success! I
appreciate your help as it made me analyze that the problem was in the
relationships and I needed to do what I could to get those working in
order to get the results I wanted.

Thanks,
Nikki