Query group by month and sum

[Adele]

Hi guys,



In my query in design view I have managed to pull through all the values for each month in 2011, by putting the following in the date field:

Like "**/01/2011"
Like "**/01/2011"
Etc.

This field is hidden, I have added another identical field with no Like instances to view the results, however I need to group the results so they don’t read:

100 04/01/2011
170 18/01/2011
90 02/02/2011
160 15/02/2011

And appear as:

270 01/2011
250 02/2011

In addition to this I need another field that sums the value of the months.

Many thanks,

Adele x

[alansidman]

Create a second query using the first one as the record source.

Here is the SQL statement for the second query.

SELECT Sum(t1.tValue) AS SumOftValue, Month([tdate]) & "/" & Year([tdate]) AS tdate1
FROM t1
GROUP BY Month([tdate]) & "/" & Year([tdate]);

where t1 is the name of your first query, tvalue is the values field and tdate is the date field. Change them to the names of your fields.

Alan

[Adele]

fantastic i'm sure that would work great however im very new to access and work in design view alone and don't really get coding... ...is there any chance you could offer instructions for a query design view?
thanks

[alansidman]

No problem, but as a point of information. You can always see the SQL statement even when you produce your query in design view. In the upper right hand corner where you change from design view to datasheet view, click on the SQL view. Having said that, look at the attached and you can see what I did in the design view.

Alan

[Adele]

thank you very much!