Default value in a query

[Shawn29316]

Hi,

I'm working with 3 tables to do a calculation and it's giving me trouble.
Example of the data:
Table A
Machine1
Machine2
Machine3

Table B
Machine1, 2 (RHours)
Machine3, 4 (RHours)

Table C
Machine2, 1 (PHours)
Machine3, 6 (PHours)

Query:
SELECT A.Machine, [b].[RHours], [C].[PHours], [b].[RHours]/([C].[PHours]+[b].[RHours]) AS [Ratio]
FROM (A LEFT JOIN B ON A.Machine = B.Machine) LEFT JOIN C ON A.Machine = C.Machine;

When I run the query I get:

Machine1, 2 (RHours), blank (PHours), blank (Ratio)
Machine2, blank (RHours), 1 (PHours), blank (Ratio)
Machine3, 4 (RHours), 6 (PHours), 0.4 (Ratio)


The whole thing would work if I could default a 0 for RHours and PHours when there was no corresponding record in the initial data table. Any suggestions on how to do that without modifying the original data?

Thanks,

[MMcCarthy]

Try this...

SELECT A.Machine, nz(B.[RHours],0), nz(C.[PHours],0), B.[RHours] / IIf((nz(C.[PHours],0) + nz(B.[RHours],0))=0,1,(nz(C.[PHours],0) + nz(B.[RHours],0))) AS Ratio
FROM (A LEFT JOIN B ON A.Machine = B.Machine) LEFT JOIN C ON A.Machine = C.Machine;
 

[Shawn29316]

Try this...

SELECT A.Machine, nz(B.[RHours],0), nz(C.[PHours],0), B.[RHours] / IIf((nz(C.[PHours],0) + nz(B.[RHours],0))=0,1,(nz(C.[PHours],0) + nz(B.[RHours],0))) AS Ratio
FROM (A LEFT JOIN B ON A.Machine = B.Machine) LEFT JOIN C ON A.Machine = C.Machine;
 

All I can say is WOW! I had given up on being able to do this.....and I'm still not sure how it worked but it did.

Thanks!
Shawn

[MMcCarthy]

All I can say is WOW! I had given up on being able to do this.....and I'm still not sure how it worked but it did.

Thanks!
Shawn

No problem Shawn.

The nz() function will return 0 if the value is null and since you can't divide a number by 0 the IIf statement checks for 0 and replaces it with 1.

Mary