I have a query for every month of the year and a report that has all 12 
months on that report. What  I would like to be able to do is to have one 
query and show each month on the report, but i would also like to able to 
input the time span that the query should calculate. I hope this make sense. 
Please let me know if this needs for explination. Thanks for your time.

On Mon, 11 Feb 2008 10:16:00 -0800, Erik <Erik@discussions.microsoft.com>
wrote:

>I have a query for every month of the year and a report that has all 12 
>months on that report. What  I would like to be able to do is to have one 
>query and show each month on the report, but i would also like to able to 
>input the time span that the query should calculate. I hope this make sense. 
>Please let me know if this needs for explination. Thanks for your time.

Please open one of your queries, select View... SQL, and post the SQL text
here.

It's certainly possible to do this using a parameter query, and you do indeed
need only one query, not 12. Rather than twelve subreports (if that's what you
now have) you can use a single Report, and use its Sorting and Grouping to
group by the month.
-- 
             John W. Vinson [MVP]

I am not to sure on how to setup the  Sorting and Grouping  feature. Is there 
any examples or information you might be able to give. Thanks

"John W. Vinson" wrote:

> On Mon, 11 Feb 2008 10:16:00 -0800, Erik <Erik@discussions.microsoft.com>
> wrote:
> 
> >I have a query for every month of the year and a report that has all 12 
> >months on that report. What  I would like to be able to do is to have one 
> >query and show each month on the report, but i would also like to able to 
> >input the time span that the query should calculate. I hope this make sense. 
> >Please let me know if this needs for explination. Thanks for your time.
> 
> Please open one of your queries, select View... SQL, and post the SQL text
> here.
> 
> It's certainly possible to do this using a parameter query, and you do indeed
> need only one query, not 12. Rather than twelve subreports (if that's what you
> now have) you can use a single Report, and use its Sorting and Grouping to
> group by the month.
> -- 
>              John W. Vinson [MVP]
> 

It depends to some degree whether you are returning rows from the table(s) or 
aggregating values.  If you are simply returning rows then you can include 
two computed columns in a query to return the Year and month (as numbers) for 
each date value, e.g.

SELECT TransactionID, TransactionDate, Amount, 
YEAR(TransactionDate) AS TransactionYear,
MONTH(TransactionDate) AS TransactionMonth
FROM Transactions;

You can then group a report, using its internal sorting and grouping 
maechanism, firstly on the TransactionYear and then on the TransactionMonth, 
giving the TransactionMonth group a Group Header.  Include the 
TransactionYear and TransactionMonth in the group header and the other fields 
in the detail section.  You could of course give both groups group headers if 
you prefer and out the year in one and the month in the other so that the 
year only prints once in the report.

If you are aggregating data you do much the same but group the query on the 
year and month, e.g.

SELECT YEAR(TransactionDate) AS TransactionYear,
MONTH(TransactionDate) AS TransactionMonth,
SUM(Amount) AS TotalMonthlyAmount
FROM Transactions
GROUP BY YEAR(TransactionDate), MONTH(TransactionDate);

With a query like this you' d probably not use group headers in the report 
at all, or just have a TransactionYear group header, putting the month and 
the total amount in the detail section.

To restrict the report to  date range you can include parameters in the 
query.  Date/time parameters are best declared as otherwise a date in short 
date format might be interpreted as an arithmetical expression rather than a 
date and give the wrong results, so taking the first query above as an 
example, adding parameters would give you;

PARAMETERS [Enter start date:] DATETIME,
[Enter end date:] DATETIME;
SELECT TransactionID, TransactionDate, Amount, 
YEAR(TransactionDate) AS TransactionYear,
MONTH(TransactionDate) AS TransactionMonth
FROM Transactions
WHERE TransactionDate >= [Enter start date:]
AND TransactionDate < [Enter end date:] + 1;

The method used her for defining the data range, returning all rows on or 
after the start date and before the day following the end date, makes sure 
that if any TransactionDate values in the table inadvertently include 
non-zero times of day (which can easily happen if you haven't taken steps in 
the table design to prevent this, i.e. a validation rule limiting the values 
to dates with non-zero times of day) and those dates fall on the final day of 
the range, are nevertheless returned.  A BETWEEN….AND operation would exclude 
such dates.

I've shown the queries as SQL here, but you can also build them visually in 
query design view of course.

You can also aggregate values in the report; for instance with a report 
based on the first query you could have group footers for the month and year, 
and a report footer and out a text box in each with a ControlSource of 
=Sum([Amount]).  This would give you sub-totals by month and year and an 
overall grand total.  You can also use other aggregation operators of course 
as well as Sum, e.g. Avg, Min or Max.

I hope that gets you started.  If you need any help applying it to your own 
database post back.

Ken Sheridan
Stafford, 

"Erik" wrote:

> I have a query for every month of the year and a report that has all 12 
> months on that report. What  I would like to be able to do is to have one 
> query and show each month on the report, but i would also like to able to 
> input the time span that the query should calculate. I hope this make sense. 
> Please let me know if this needs for explination. Thanks for your time.