R
rotata
I am working in Access 2003 on an XP.
I have a database where one table lists "crews" and the other lists
"employees".
The crew has a field which shows the authorized FTE (full time equivalent).
The Employee table has a field which list their FTE (either 1 or .5).
The two tables are related in a one-to-many relationship (crew to
employees), where both values must be equal.
I then made standard queries of each table.
Then, I made a third query (based on the first two) and related the
first two queries as "all crews" and "employees that match". On this, I
group the employee fte, sum the employee fte, and count the employee
fte. This gives me a query which lists all of my crews and who is on
each. (I can get body count or an FTE). This query also lists crews in
which there is no one assigned.
Next, I made a report summing the fte and subtracting that from the
authorized.
Everything is great so far. I am able to figure out which crews have too
many fte and which have too few.
Unfortunately, when there is a crew with an authorized 1 fte AND there
is no one on that crew, then the calculation does not show a shortage.
It doesn't show anything. Apparently, taking nothing from 1 equals
nothing instead of 1. Reversing the equation doesn't help. Taking 1 from
nothing still equals nothing.
I guess it comes down to this, How do I make the null value in the
combined query to output a zero instead of a null value?
Thanks,
Jan
I have a database where one table lists "crews" and the other lists
"employees".
The crew has a field which shows the authorized FTE (full time equivalent).
The Employee table has a field which list their FTE (either 1 or .5).
The two tables are related in a one-to-many relationship (crew to
employees), where both values must be equal.
I then made standard queries of each table.
Then, I made a third query (based on the first two) and related the
first two queries as "all crews" and "employees that match". On this, I
group the employee fte, sum the employee fte, and count the employee
fte. This gives me a query which lists all of my crews and who is on
each. (I can get body count or an FTE). This query also lists crews in
which there is no one assigned.
Next, I made a report summing the fte and subtracting that from the
authorized.
Everything is great so far. I am able to figure out which crews have too
many fte and which have too few.
Unfortunately, when there is a crew with an authorized 1 fte AND there
is no one on that crew, then the calculation does not show a shortage.
It doesn't show anything. Apparently, taking nothing from 1 equals
nothing instead of 1. Reversing the equation doesn't help. Taking 1 from
nothing still equals nothing.
I guess it comes down to this, How do I make the null value in the
combined query to output a zero instead of a null value?
Thanks,
Jan