Problem with a MS Access self-join

[ricksil]

I'm having some trouble with this MS Access query:

select  t.code1,
        sum(nz(iw.qty,0)) as iwcnt,
        sum(nz(t.qty,0)) as totcnt
from    quantities iw,
        quantities t
where   t.code1 = iw.code1
and     iw.code2 = 'iw'
group by t.code1;

In the quantities table there is a column called code2. I am joining the quantities table to itself because I am trying to see what percentage has a code2 of "iw". For example, our data looks like this:

code1   code2   qty
-----   -----   ---
  56       iw     3
  56       xx    11

So with my query, I was hoping to return:

code1   iwcnt   totcnt
-----   -----   ------ 
   56       3       14

This means that 3 out of 14 were for code2 of "iw". The t table holds the total data and the iw table holds the data just where code2 = "iw".

But instead of returning 3 out of 14, it is returning 22 out of 14!:

code1   iwcnt   totcnt
-----   -----   ------ 
   56      22       14

Does anybody have any idea why?

[CJ_London]

try

from quantities iw inner join quantities t on t.code1 = iw.code1
where iw.code2 = 'iw'

[ricksil]

No joy. I tried:

select  t.code1,        sum(nz(iw.qty,0)) as iwcnt,
        sum(nz(t.qty,0)) as totcnt
from    quantities iw inner join quantities t on (t.code1 = iw.code1)
and     iw.code2 = 'iw'
group by t.code1;

and I got the same results.

[DrGUI]

Try this:

SELECT code1, sum(qty) AS iwcnt, (SELECT sum(qty) FROM tbl_Codes) AS totcnt FROM tbl_Codes
WHERE code2="iw" group by code1

[ricksil]

That worked! Thanks so much, Doc.

Any idea why my original approach doesn't work?