count for each month

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

You are getting one row, because you have restricted result with month=1 in having clause. Remove that and you should get 12 rows.

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));

yes you are righ !!! :-(((

and have you any idea to return a 0 if the count is empty

if a few month are missing then I have not 12 rows

thanks again !!

Not very straight forward. Try this.

SELECT Year([DateMessages]), Months.Month,
Count(Messages.Id) AS CountOfId
FROM Messages INNER JOIN List_Members_3 ON Messages.Id = List_Members_3.Id
RIGHT JOIN (SELECT 1 as month
union
select 2
union
select 3
union
select 4
union
select 5
union
select 6
union
select 7
union
select 8
union
select 9
union
select 10
union
select 11
union
select 12) as months
ON Month([DateMessages]) = months.month
GROUP BY Year([DateMessages]), Months.Month
HAVING (((Year([DateMessages]))=2004));

This look terrible ! I couldn't imagine such a possibilty !

I get an error >>>

Operator absent in :
Messages.Id = List_Members_3.Id
RIGHT JOIN (SELECT 1 as month
union
select 2
union
select 3
union
select 4
union
select 5
union
select 6
union
select 7
union
select 8
union
select 9
union
select 10
union
select 11
union
sele

Access needs parentheses for multiple table joins. Try this.

SELECT Year([DateMessages]), Months.Month,
Count(Messages.Id) AS CountOfId
FROM Messages INNER JOIN (List_Members_3
RIGHT JOIN (SELECT 1 as month
union
select 2
union
select 3
union
select 4
union
select 5
union
select 6
union
select 7
union
select 8
union
select 9
union
select 10
union
select 11
union
select 12) as months
ON Month([DateMessages]) = months.month)
ON Messages.Id = List_Members_3.Id
GROUP BY Year([DateMessages]), Months.Month
HAVING (((Year([DateMessages]))=2004));

ho ! I get again an error >>>(expression de jointure non supportee) that's mean something like >>> Join expression non valid

it was great !! :-(( but access doesn't want

Looks like access does not like derived table. To workaround this you can create a query for

SELECT 1 as month
union
select 2
union
select 3
union
select 4
union
select 5
union
select 6
union
select 7
union
select 8
union
select 9
union
select 10
union
select 11
union
select 12

and use the query in RIGHT JOIN.

SELECT 1 as month from which table ?

sorry I am not used to so advanced SQL Queries and I don't know what are exactly thoses 1,2,3 as months .. from which table

to simplify , the tables >>>

Members :

Members_Id
Cat
-----
Messages :

Messages_id
Members_Id
DateMessages
----------
a procedure >>>

List_Members_3 :

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

then I need for YEAR(Messages.DateMessages)= 2004
each Month MONTH(Messages.DateMessages)= 1 to 12




:-)

I did not test my query against access, it works in SQL Server.

It seems access does not support SELECT without table name. In that case you have to build a table with 12 rows, one for each month. And join it with rest of the tables.

ok ! thanks a lot for your time !