I have 2 tables in Access 2000 : Members and Messages


I get all the members.Id for a category with a Procedure

List_Members_3 >>>

SELECT Members.Members_Id
FROM Members
WHERE Members.Cat = 3


then I want to get all the Messages of the Members for january 2004

SELECT Count(Messages.Id) AS CountOfId
FROM Messages INNER JOIN List_Members_3 ON Messages.Id = List_Members_3.Id
GROUP BY Year([DateMessages]), Month([DateMessages])
HAVING (((Year([DateMessages]))=2004) AND ((Month([DateMessages]))=1));


I get one row = Count

how can I get 12 rows for each month ?

Month([DateMessages])=(1 to 12)

-------------------------

and how can I avoid >>>

SELECT Count(Messages.Id) AS CountOfId
FROM Messages INNER JOIN List_Members_3 ON Messages.Id = List_Members_3.Id

with sommething like >>>

SELECT Count(Messages.Id) AS CountOfId
FROM Messages Where Messages.Members_Id IN (SELECT Members.Id
FROM Members
WHERE Members.Cat = 3)


thank you

use an integers table:

create table integers ( i integer )
insert into integers values ( 1 )
insert into integers values ( 2 )
insert into integers values ( 3)
insert into integers values ( 4)
insert into integers values ( 5)
insert into integers values ( 6)
insert into integers values ( 7)
insert into integers values ( 8)
insert into integers values ( 9)
insert into integers values ( 10 )
insert into integers values ( 11 )
insert into integers values ( 12 )

then use a LEFT join from the integers to your data using i to match the month number

it seems absolutly crazy and fantastic ! ::-))

thank you !!!