Problem with Query Calculation (Part 2)

[Rene Lazaro]

Good morning,

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

Column 1 (Group By) TheMonth: Month([tbl_injuries].[injury date])

Column 2 (Group By) TheYear: Year([tbl_injuries].[injury date])

Column 3 (Group By) [Parent Company] (There is a relationship between two tables. Please see my comment at the bottom.)

Column 4 (Expression) LTI Mth: Sum([tbl_Injuries]![Injury Classification]="Lost Time")*-1

Column 5 (Expression) LTI Ytd: DSum("[Injury Classification]='Lost Time'","tbl_Injuries","Format([Injury Date], 'mm')<= '" & [Month] & "'")*-1

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

Note:

The Facility Table is related to the Injury Table via a field called [Insurance Code]. The Facility Table contains the [Parent Company].

[Injury Classification] is a text field. When I run the query, it returns the sum as a negative number, hence the -1 multiplied to the expression. (There may be a more appropriate way of doing this?)

Issue:

The query provides a sum of injuries 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

 

[DrSnuggles]

Quickly noticed there is a space in the form reference:
Column 6 (Where) [Injury Date] Between [Forms]![ Date Range]![Start Date] And [Forms]![Date Range]![End Date]

Also I would slowly build up the Query (add one table at a time) so that you know that the joins are correct and thus the right data is being returned.