Building a Total Query

[Guest]

I have a table that contains monthly totals for categories of information.

Example Fields and Data and Data Type:

MonthYear June 2005 Date
CallsForService 100 Num Int
Citations 135 Num Int
OWLS 11 Num Int
Warnings 70 Num Int
Warrants 15 Num Int

There is one record for each month of the year.

Now for the query...I need to add up 12 months of data on a monthly basis.
For examples, I need to add up June 2004 to May 2005. Then for the next
month I need May 2004 to June 2005. And so on.

I tried building the query with MonthYear and categirt fields listed. Then
added the Total Row and SUMed each Category. I tried to use GROUP BY on
MonthYear, but of course that did not work since there is only one record per
month.

I thought about DataAdd function, but couldn't pull anything together with
that either.

Sooo...I'm lost. I would appreciate help with this.

Thanks!
Santara

 

[John Vinson]

I have a table that contains monthly totals for categories of information.

Example Fields and Data and Data Type:

MonthYear June 2005 Date
CallsForService 100 Num Int
Citations 135 Num Int
OWLS 11 Num Int
Warnings 70 Num Int
Warrants 15 Num Int

There is one record for each month of the year.

Now for the query...I need to add up 12 months of data on a monthly basis.
For examples, I need to add up June 2004 to May 2005. Then for the next
month I need May 2004 to June 2005. And so on.

I tried building the query with MonthYear and categirt fields listed. Then
added the Total Row and SUMed each Category. I tried to use GROUP BY on
MonthYear, but of course that did not work since there is only one record per
month.

Use the WHERE operator on the Totals row for MonthYear, and on the
criteria line under it put

BETWEEN DateSerial(Year(Date()) - 1, Month(Date()), 1)
AND DateSerial(Year(Date()), Month(Date()) - 1, 1)


John W. Vinson[MVP]

 

[Guest]

John,

I know I'm missing something here. I input the Criteria "exactly" as you
showed.

1) If I change the operator from GROUP BY to WHERE, I get the following
error. "You tried to execute a query that does not include the specified
expression 'tblBoardReport.MonthYear' as part of an aggregate function."

2) If I use the GROUP BY operator, the query will run and give the past 12
months of records for September 2004 to August 2005. They are listed as
individual records, not as one Totaled Record.

3) Where, and how, in the supplied criteria, do I indicate the Month and
Year that I am searching for...like May 2004 to June 2005. I need to
indicate which 12 month period for the query to use.

4) Since the WHERE operator produces an error, how can I get this SUM up the
12 months of data?

Thank you for your help.

Santara

 

[John Vinson]

John,

I know I'm missing something here. I input the Criteria "exactly" as you
showed.

1) If I change the operator from GROUP BY to WHERE, I get the following
error. "You tried to execute a query that does not include the specified
expression 'tblBoardReport.MonthYear' as part of an aggregate function."

It sounds like you're trying to display the MonthYear as well. Don't.
That field should not use the GROUP BY operator, and should have its
"show" checkbox unchecked; it should also not be used in any
expressions.

2) If I use the GROUP BY operator, the query will run and give the past 12
months of records for September 2004 to August 2005. They are listed as
individual records, not as one Totaled Record.

Exactly - that's what Group By is designed for.

3) Where, and how, in the supplied criteria, do I indicate the Month and
Year that I am searching for...like May 2004 to June 2005. I need to
indicate which 12 month period for the query to use.

Try again using the WHERE operator, with a criterion of

= DateSerial([Enter starting year:], [Enter starting month number:], 1) AND < DateSerial([Enter starting year:] + 1, [Enter starting month number:], 1)

4) Since the WHERE operator produces an error, how can I get this SUM up the
12 months of data?

By correcting the error... <g>

If you're still having problems please post the SQL view of the query.

John W. Vinson[MVP]