Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2014
    Posts
    2

    Unanswered: Group by and Count

    I've used Access 2000 for some basic data duties (many years). I'm not handy with SQL etc, but I know how to build very basic easy queries.

    I have data in two fields
    date and number

    they look like this
    1/3/1982 9
    1/4/1982 8
    1/5/1982 15
    2/4/1988 6
    2/15/1988 7
    2/22/1988 9
    2/23/1988 11

    I want my query to output by year the count of the days in a certain month(January)have a number value of < 10.
    So the output of the above data would be
    1982 2
    1988 3

    I know how to show all those records by using criteria 1/**/**** but I'm all mixed up on how do a grouping(by year) and a count of that grouping.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try:
    Code:
    SELECT Year(Table1.Date), Count(Table1.Number)
    FROM Table1
    WHERE Table1.Number < 10
    GROUP BY Year(Table1.Date);
    Note: Be careful when you choose the columns name: Date is a reserved word in Access.
    Have a nice day!

  3. #3
    Join Date
    Jan 2014
    Posts
    2
    perfect!

    but what if I want to grab the ># from a series of months,
    right now if i want to find all the >#'s for January I put
    Like "1/**/****" in
    what is the shortcut to do a like say for *1 to *4/**/****
    Last edited by glenncz; 01-30-14 at 20:35.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could use:
    Code:
    WHERE Month(Table1.Date) IN ( 1, 2, 3, 4 )
    Or:
    Code:
    WHERE Month(Table1.Date) BETWEEN  1 AND 4
    OR
    Code:
    WHERE Month(Table1.Date) < 5
    etc...
    Have a nice day!

Posting Permissions

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