A doozy of a report

[It_thug]

Okay, I have a doozy of a report, and I'm not sure where to begin on this. Any suggestions would be greatly appreciated.

I was originally making a bunch of queries, but then as it got more complex, I started wondering if there was a better way to go about this. So, I don't need a complete solution or anything like that. But just a nudge in the right direction would be greatly appreciated.

Here's the situation.

I have a Query that all this will work off of.

The query has Item Ideas, purchase date, amount, and cost.

I need to Have it broken down as follows.

I need a month to month break down of what was purchased in that month. (There are 4 years of data). This just seems really complicated as I'd have to create separate queries than the one that totals for the year. Because I can't get it to SUM correctly with the transaction dates as part of the query.

I need a total by year for each item. (got this with no problems.)

The problem that I'm running into is that I need to be able to see the break down by month. Which I guess is possible with a whole bunch of queries, but there has to be a better way to do this.

Any help or nudge in the right direction would be GREAT.

 

[Galaxiom]

Create a query replacing the date with a derived monthyear field using:
DatePart(Datefield, "m") & DatePart(Datefield, "y")

Group by this field.

 

[It_thug]

Create a query replacing the date with a derived monthyear field using:
DatePart(Datefield, "m") & DatePart(Datefield, "y")

Group by this field.

perfect. Thanks much.

Took me a little while to get that to work(my bad, not yours), but once I did, it's doing exactly what I want it to.

Thanks again.

 

[Galaxiom]

Looking at it now I must have had DatePart function on my mind at the time.
Could also be done with Month(Datefield) & Year(DateField).

Exactly the same result and Access probably runs much the same code behind the scenes but I thought I would mention it.