Here is one...
create table t1(Item char, Mon varchar(3), Num int)
insert t1 select 'A', 'Jan' , 3
union all select 'B', 'Jan' , 2
union all select 'C', 'Jan' , 1
union all select 'A', 'Jan' , 6
union all select 'B', 'Jan', 11
union all select 'C', 'Jan' , 8
union all select 'B', 'Feb', 4
union all select 'B', 'Feb' , 6
union all select 'A', 'Mar' , 7
union all select 'C', 'Mar' , 9
union all select 'A', 'Mar', 12
select t2.Item,t3.Mon,count(t1.Item) cnt
from t1 right join (select distinct Item from t1)t2 cross join (select distinct Mon from t1)t3 on t1.Item=t2.Item and t1.Mon=t3.Mon
group by t2.Item,t3.Mon
drop table t1
RAC v2.2 & QALite!
"Rich Protzel" <com> wrote in message news:#phx.gbl...
Here is my source data table (tbl1)
Item Month Num
A Jan 3
B Jan 2
C Jan 1
A Jan 6
B Jan 11
C Jan 8
B Feb 4
B Feb 6
A Mar 7
C Mar 9
A Mar 12
If I count this data by Item by month, A and C will not show up for Feb.
I need A and C to show up for Feb with a count of 0.
I tried creating a list table (tblABC) containing all the Items in a
unique field called "Item" and then doing a count by joining tbl1 to
tblABC on the Item field. Items A and C still did not show up with a
count of 0 for Feb. I want to include all Rows in tblABC for all months
in tbl1. I tried Right Join, still nothing.
Select t1.Item, Sum(t1.num) from tbl1 t1 Join tblABC Group by Item,
Request any suggestions how to achieve returning all Items wether 0 or
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!