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,
