Group dates

G

Guest

I need to write a query (in design view) which will sum the total account
value for each customer, split by month. The date field in my database
(it_date) is in the format of dd/mm/yyyy, with the 'dd' always set to the
last day of the relevant month, so all Jan 2007's will be displayed as
31/01/2007, all Feb 2007's will be displayed as 28/02/2007, etc.
What expression do I need to group the amounts (it_amount) by month (looking
only at dates >01/01/2005), so that the results look similar to below?

Customer Ref Date Total Account Value
1234 Jul-07 500
1234 Jun-07 400
2468 Jul-07 300
2468 May-07 333

Thanks in anticipation.
 
J

John Spencer

You can use the FORMAT function to get the date to display as month and
year.

Format(It_Date,"mmm\-yy") will do it for month abbreviation followed by
2-character year.

If you want to sort in date order you will need to retain the date field in
addition to the calculated field. When you format a date (or number), it
gets turned into a string and is sorted alphabetically - Dec is before Nov.

If you are asking how to build an aggregate (totals) query and not how to
format the date, then post back.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top