Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2007
    Posts
    47

    Unanswered: need help with date field

    Hi,

    I have a query as below,

    select audittype, reviewtype, auditdate from auditdb where condition...........
    group by auditdate; (in SQL)

    How do I find the number of audits in each month from the field auditdate. Auditdate is in the format 01/01/2007. I have thousand of records in the database.

    Thanks in advance.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    which database system are you using?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Posts
    47
    I am using MS Access

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Assuming you have some type of ID field for each audit...

    SELECT MONTH(AUDITDATE) AS MNTH,
    YEAR(AUDITDATE) AS YR,
    COUNT(AUDITID) AS AUDITS
    FROM AUDITDB
    GROUP BY MONTH(AUDITDATE), YEAR(AUDITDATE)
    ORDER BY YEAR(AUDITDATE), MONTH(AUDITDATE)
    Inspiration Through Fermentation

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thread moved to ms access forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2007
    Posts
    47

    help with date field

    Hi,

    I have a query as below,

    select audittype, reviewtype, auditdate from auditdb where audittype="appraiser" and reviewtype="frontend" or "backend" group by auditdate;

    How do I find the number of audits in each month from the field auditdate. Auditdate is in the format 01/01/2007. I have thousand of records in the database. Like in the month of Jan how many frontend and backend audit I have.

    Thanks in advance.

  7. #7
    Join Date
    Jan 2007
    Posts
    47
    Thank u. It works now.

  8. #8
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    To get the month number out of the auditdate field, you can use the month() built-in function. AuditMonth: Month(AuditDate)
    That will give you the month number. Best for sorting. If you want the month name (better done in the report, or on the form) you can use the Format function.
    HTH,

Posting Permissions

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