Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Posts
    5

    Question Unanswered: Calculate records per month

    Hi all.
    I need to calculate how many records I have in each month and create table based on this summary result.
    I appreciate any help.

  2. #2
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    You can create a 'totals' query. 2 columns... 1 column set to count, then the next column will 'Group By' your date field. To group by month you would do something like:

    Month: Format([yourdatefield], "mm")

    SQL would look something like this

    Code:
     
    SELECT Count(tblInvoice.InvoiceNo) AS CountOfInvoiceNo, Format([DueDate],"mm") AS [Month]
    FROM tblInvoice
    GROUP BY Format([DueDate],"mm");
    HTH,

    Chris

  3. #3
    Join Date
    Aug 2004
    Posts
    5
    Thanks a lot. It work. Thanks.

  4. #4
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    Anytime, glad it helped.

  5. #5
    Join Date
    Oct 2003
    Posts
    706
    A nice side-effect of GROUP BY queries is that a single query can calculate totals for every month that's present in the input data, all at once!

    The essential concept behind GROUP BY is that every distinct value (or distinct combination of values, if grouping by more than one column at a time) defines a separate group of records, and statistics such as COUNT, SUM, MIN, MAX, etc. are calculated for each group.

    In this case, GROUP BY on a calculated-field which (by whatever formula) produces the same result given any date in a particular month, neatly defines a "group" for per-month calculations.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

Posting Permissions

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