0

I have a query (in MS-Query, MSQRY32.EXE) that does 2 sums, and then the first will subtract the second, but in some cases the second is null, how do I go over this?

(Sum(CASE WHEN m.mcdmv=11 THEN m.mqtd END)- 
Sum(CASE WHEN m.mcdmv=12 THEN m.mqtd END))

This is the part of the query, but sometimes one of them or both are null, so how do I do this?

BIBD
  • 15,107
  • 25
  • 85
  • 137
Totty.js
  • 15,563
  • 31
  • 103
  • 175
  • 2
    are you really using Access? Is this a pass-through query? Access doesn't use CASE it uses IIF – Leslie Apr 12 '11 at 16:42
  • yes, it works if i dont try to subtract. only i get null values when one of the sum contains null... – Totty.js Apr 12 '11 at 16:44
  • Perhaps you better show us a fuller example of your query. You can skip the irrelevant columns. – BIBD Apr 12 '11 at 17:06
  • Lordy... anything we can do to convince you to do the query natively in MS Access, and then query the resulting query in MS Query / Excel (Whatever app you are launching MS Query from)? – BIBD Apr 13 '11 at 13:12
  • Now I realize that I need to use MS query, because I can autoupdate the excel data on fly. no copy paste... – Totty.js Apr 13 '11 at 16:11

2 Answers2

1

(IIf(Sum1 Is Null,0,Sum1) - IIf(Sum2 Is Null,0,Sum2))

Thebigcheeze
  • 3,408
  • 2
  • 22
  • 18
  • but in my access I can't do "Sum(CASE WHEN m.mcdmv=11 THEN m.mqtd END) as 'Sum1'"... it just dont accept it! – Totty.js Apr 12 '11 at 16:47
  • In that case, this might be a better fit: `(nz(Sum1,0) - nz(Sum2,0))` – Thebigcheeze Apr 12 '11 at 16:54
  • I'm sorry, I was perhaps unclear in my haste. Here's how I would envision the solution: `(nz(Sum(CASE WHEN m.mcdmv=11 THEN m.mqtd END),0) - nz(Sum(CASE WHEN m.mcdmv=12 THEN m.mqtd END),0))` Does this work? – Thebigcheeze Apr 12 '11 at 17:01
  • nop, i think I dont have the nz function... or so. is access 2003 – Totty.js Apr 12 '11 at 17:03
  • It has nz(), I guarantee it. If it didn't, MS would have broken millions of DBs – BIBD Apr 12 '11 at 17:08
  • Perhaps a better solution would be to store the results of the sums into local variables `Sum1 = ...` and `Sum2 = ...` and then using the IIf syntax in the original post. – Thebigcheeze Apr 12 '11 at 17:12
  • 2
    again, i don't see how your original query is working using a CASE statement in Access....Access doesn't have it, it uses IIF() – Leslie Apr 12 '11 at 19:04
  • 1
    I dont know, but in the window panel the title is: "Microsoft query". So its not exactly access. You are right in access it gives me an error at the "case" – Totty.js Apr 13 '11 at 08:09
0

First I'd ask whether it is appropriate to be summing nulls, and what you expect to get when you subtract a null from an non-null (or vice-versa).

Probably what you want to do is either eliminate records with nulls (where m.mqtd is not null) or to change all the null's to 0's (nz(m.mqtd, 0))

BIBD
  • 15,107
  • 25
  • 85
  • 137
  • yup... read only - just use it in the select wherever you are using m.mqtd – BIBD Apr 12 '11 at 16:53
  • (Sum(CASE WHEN m.mcdmv=11 THEN (Nz(m.mqtd, 0)) END) - Sum(CASE WHEN m.mcdmv=12 THEN (Nz(m.mqtd, 0)) END)) ||| it doesnt work – Totty.js Apr 12 '11 at 16:55