Problem with Query Calculation

[Rene]

Good afternoon,

The query has the following six columns (Totals Query):

Column 1 (Group By) Month: Format([Begin],"mm")

Column 2 (Group By) Year: Year([Begin])

Column 3 (Group By) [Parent Company]

Column 4 (Sum) [Hours]

Column 5 (Expression) Hours Ytd:
DSum("Hours","tbl_Manhours","Format([Begin], 'mm')<= '" & [Month] & "'")

Column 6 (Where) [Begin] Between [Forms]![ Date Range]![Start Date] And
[Forms]![Date Range]![End Date]

The query provides a sum of the hours by month, for the months that fall
between the date ranges I specify on the parameter form. The problem is the
expression.

The expression provides a running (aggregate) total for each month. However,
it provides a total of ALL fields in the source table. I need the running
total to be based ONLY on the date ranges I specify.

For instance, if I specify between 01-01-09 and 06-30-09, It should
aggregate the totals for these months only. And not give me everything.

Please help.

Rene

 

[John W. Vinson]

Good afternoon,

The query has the following six columns (Totals Query):

Column 1 (Group By) Month: Format([Begin],"mm")

Column 2 (Group By) Year: Year([Begin])

Column 3 (Group By) [Parent Company]

Column 4 (Sum) [Hours]

Column 5 (Expression) Hours Ytd:
DSum("Hours","tbl_Manhours","Format([Begin], 'mm')<= '" & [Month] & "'")

Column 6 (Where) [Begin] Between [Forms]![ Date Range]![Start Date] And
[Forms]![Date Range]![End Date]

The query provides a sum of the hours by month, for the months that fall
between the date ranges I specify on the parameter form. The problem is the
expression.

The expression provides a running (aggregate) total for each month. However,
it provides a total of ALL fields in the source table. I need the running
total to be based ONLY on the date ranges I specify.

For instance, if I specify between 01-01-09 and 06-30-09, It should
aggregate the totals for these months only. And not give me everything.

Change the expression to explicitly include the date range. The DSum is
summing the values from the entire table; its criteria are independent of the
criteria in the outer query. What result do you want if (say) [Start Date] is
December 1, 2007 and [End Date] is January 30, 2008? Which records should be
included in the year to date?

Also change the names of your Month and Year fields. They're reserved words
and WILL get you into trouble.

 

[Rene]

Good morning John,

How do I write the expression to explicitly include the date range? (I've
tried already and don't seem to get it right.)

The result I want is a cumulative (Year to Date) total by month. So, a
parameter of December 1, 2007 to January 30, 2008, should yield for example:

Month: Dec 07 = 10, Jan 08 = 20, Feb 08 = 30, March 08 = 40, etc.

The formula should sum the above provide a cumulative total as follows:

Year to Date: Dec 07 = 10, Jan 08 = 30, Feb 08 = 60, March 08 = 100, etc.

I will change the names of the Month and Year fields. Thanks for the advice.

I really appreciate your help with this.

Rene Lazaro

Good afternoon,

The query has the following six columns (Totals Query):

Column 1 (Group By) Month: Format([Begin],"mm")

Column 2 (Group By) Year: Year([Begin])

Column 3 (Group By) [Parent Company]

Column 4 (Sum) [Hours]

Column 5 (Expression) Hours Ytd:
DSum("Hours","tbl_Manhours","Format([Begin], 'mm')<= '" & [Month] & "'")

Column 6 (Where) [Begin] Between [Forms]![ Date Range]![Start Date] And
[Forms]![Date Range]![End Date]

The query provides a sum of the hours by month, for the months that fall
between the date ranges I specify on the parameter form. The problem is the
expression.

The expression provides a running (aggregate) total for each month. However,
it provides a total of ALL fields in the source table. I need the running
total to be based ONLY on the date ranges I specify.

For instance, if I specify between 01-01-09 and 06-30-09, It should
aggregate the totals for these months only. And not give me everything.

Change the expression to explicitly include the date range. The DSum is
summing the values from the entire table; its criteria are independent of the
criteria in the outer query. What result do you want if (say) [Start Date] is
December 1, 2007 and [End Date] is January 30, 2008? Which records should be
included in the year to date?

Also change the names of your Month and Year fields. They're reserved words
and WILL get you into trouble.