Different filters for same field in one query

[pmatush]

So I have a query that gives me the total spent on different projects. We're now interested in how the costs come in over time. I can set a filter on the date field so I see all costs since the beginning of 2012, for example. However, I would like the query to return different ranges in different fields. ie., one field has costs for last six months, another shows costs for the last year, and another over the last two years, instead of running the query 3 different time with different ranges on the dates.



Thanks,
Patrick

[orange]

see if the Partition function helps.
Here's a link with sample

http://bytes.com/topic/access/insigh...ition-function

[pmatush]

That's not quite what I need. I'm not looking to necessarily break up the data into specific range lengths. The past year includes the past six months, for example. There has to be a way to have multiple cost summation columns, with different date ranges on each.

Thanks though

[pmatush]

Self answer:

Set totaling type to expression.
In field 1: Last year: Sum(IIf([Date]>#2/1/2012#,[costs],0))
In field 2: Last two years: Sum(IIf([Date]>#2/1/2011#,[costs],0))

Hope this helps someone else...