# Thread: count for each month

1. Registered User
Join Date
Mar 2004
Posts
18

## Unanswered: 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

2. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
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

PHP Code:
``` select i as month      , Count(Messages.Id) as CountOfId   from integers left outer   join Messages      on i = Month(DateMessages)    and Year(DateMessages) = 2004     inner   join List_Members_3      on Messages.Id = List_Members_3.Id group      by i  ```

3. Registered User
Join Date
Mar 2004
Posts
18
it seems absolutly crazy and fantastic ! ::-))

thank you !!!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•