Results 1 to 3 of 3
  1. #1
    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. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    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
  •