By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,921 Members | 1,433 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,921 IT Pros & Developers. It's quick & easy.

Default value in a query

P: 33
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,
Feb 28 '07 #1
Share this Question
Share on Google+
3 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Try this...

Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM (A LEFT JOIN B ON A.Machine = B.Machine) LEFT JOIN C ON A.Machine = C.Machine;
  3.  
Feb 28 '07 #2

P: 33
Try this...

Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM (A LEFT JOIN B ON A.Machine = B.Machine) LEFT JOIN C ON A.Machine = C.Machine;
  3.  
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
Feb 28 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Mar 1 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.