Show summed annual total and current month value on same row

[WhatTheFrick]

Using Access 2007 w/ mdb in 2003 format. Trying to create report that, per person, and on the same line, has an annual total (sum(MoneyField) where DateField between #1st Date# and #last Date#) and also the CURRENT month value (this comes from a control on a form, so MoneyField where DateField = FormControl).



So, in a table that has fields:

Person
Money
Date

I need to have report that shows:

Person AnnualTotal CurrentMonthValue

I can't use saved queries, I need to do this w/ SQL strings at runtime (although typically to get my strings I just design queries and then view them in SQL view).
I tried using calculated controls, but could not get this to work.

Can someone advise on best way to do this? Do I need to nest queries, or use subreports?

Thanks for any help

[June7]

Do you want the report to show only one line per person?

Options:

1. Do an aggregate query of the filtered dataset that sums by person.

2. Use domain aggregate function (DSum, DCount, DAvg, etc).

3. Combination of the above.

Why can't you use saved queries? What are you basing the report on? Using code to modify the RecordSource SQL?

Can't be more specific without a better understanding of your database.