# Thread: Calculate records per month

1. Registered User
Join Date
Aug 2004
Posts
5

## 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. Registered User
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. Registered User
Join Date
Aug 2004
Posts
5
Thanks a lot. It work. Thanks.

4. Registered User
Join Date
Sep 2003
Location
MN US
Posts
313

5. Registered User
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.

#### Posting Permissions

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