Results 1 to 11 of 11

Thread: count for each month

  1. #1
    Join Date
    Jan 2004
    Location
    France
    Posts
    43

    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. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    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));

  3. #3
    Join Date
    Jan 2004
    Location
    France
    Posts
    43

    ooops!

    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 !!

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    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));

  5. #5
    Join Date
    Jan 2004
    Location
    France
    Posts
    43
    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

  6. #6
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    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));

  7. #7
    Join Date
    Jan 2004
    Location
    France
    Posts
    43
    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

  8. #8
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    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.

  9. #9
    Join Date
    Jan 2004
    Location
    France
    Posts
    43
    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




    :-)

  10. #10
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    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.

  11. #11
    Join Date
    Jan 2004
    Location
    France
    Posts
    43
    ok ! thanks a lot for your time !

Posting Permissions

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