RE: Adding column values and displaying result in new column

[SltPhx]

Hello All..

Query 1
UserID P1 H1 E1
abc12 1 1 1
bca21 1 1 1

Query 2
UserID P2 H2 E2
abc12 1 1 1
lmn00 1 1 1

Query 3
UserID P3 H3 E3
abc12 1 1 1
cde11 1 1 1

Legend
abc12
bca21
cde11
def22

Query 4 includes ALL records from Legend and only those records from Query 1,2 and 3 where the joined fields are equal. I have a Query4 that combines all these:-
UserID P1 H1 E1 P2 H2 E2 P3 H3 E3
abc12 1 1 1 1 1 1 1 1 1
bca21 1 1 1 (blank) (blank)
cde11 (blank) (blank) 1 1 1
def22 (blank) (blank) (blank)



Result I get is:-
UserID P H E
abc12 3 3 3
bca21 (blank)
cde11 (blank)
def22 (blank)

What I want is:-
UserID P H E
abc12 3 3 3
bca21 1 1 1
cde11 1 1 1
def22 0 0 0

My problem is, I want to add 3 fields P(P1+P2+P3),H(H1+H2+H3), and E(E1+E2+E3) in Query4 and I used the formula and format but I get an answer only for abc12, for the rest of the User IDs nothing is being displayed - its blank...

I am not sure why.. From what I understand, I need to display 0 (zero) whereever it is blank before calculating P,H and E

If,
UserID P1 H1 E1 P2 H2 E2 P3 H3 E3
abc12 1 1 1 1 1 1 1 1 1
bca21 1 1 1 0 0 0 0 0 0
cde11 0 0 0 0 0 0 1 1 1
def22 0 0 0 0 0 0 0 0 0

Then, I get what I want...

Is there any way to display zero, in the blank spaces? If there is a better way to get the required answers, could anyone let me know how to do it?
Sorry for making this post really long..
Thanks much for your time and effort...

Warmest Regards,
SLTPHX

[boblarson]

Anything plus NULL equals NULL. So you have to use the NZ function to replace Nulls with 0 to add them together and get a value of 0.

Nz(P1,0) + Nz(P2,0) + Nz(P3,0)

[SltPhx]

Thank you so much!!!! That worked!
Did not know about NZ function .. and it solved so many ofmy other problems.. Thanks much for your time

Warmest Regards,
SLTPHX

[boblarson]

Thank you so much!!!! That worked!
Did not know about NZ function .. and it solved so many ofmy other problems.. Thanks much for your time

Warmest Regards,
SLTPHX

No problem. There's a lot to learn about Access and you acquire it as you need it. At least that has been my experience.