Solved: Access: group by field in a report

[Zack Barresse]

Hi all,

I'm trying to create a report based on a small database of info imported from Excel. What I want to do is have the report totalled by month. I created a new field for the main table with an update query that added just the month name (don't know if I need that to total by month or not, there is a date field as well). I've uploaded the database to give a better example.

What I want to end up with is a report that shows a Monthly total for each Source in the database. There are five sources, so this should be 60 different totals. There is a field in the database called 'galTKN', this value should appear in that summary as well. The value will not change and is the same for every record. I was debating creating an extra table for this, but it could change in the future.

Let me know if you need any other information. TIA.

 

[Zack Barresse]

Bump.

 

[cristobal03]

Wow, lol. Sorry nobody's posted on this yet Zack. I've been off the boards, I'll have a look at it.

chris.

 

[cristobal03]

Hehe, or maybe I won't. Any chance of getting that in a 2000 version? Or is there unconvertable code (though I doubt the code should matter)?

chris.

 

[Zack Barresse]

Actually, with the help of pbaldy I got it...

SELECT Format([galDate],"mmm yyyy") AS TheMonth, tblGallons.galSource, Sum(tblGallons.galTKN) AS SumOfgalTKN, Sum(tblGallons.galGallons) AS SumOfgalGallons
FROM tblGallons
GROUP BY Format([galDate],"mmm yyyy"), tblGallons.galSource, Format([galdate],"yyyymm")
ORDER BY Format([galdate],"yyyymm");