Problem with Query Calculation

[Rene Lazaro]

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

 

[jzwp22]

If you want to reset the running sum for each month and for each parent company and only for the period of interest, then you must also include those criteria in the DSUM() expression. Something along these lines:

SELECT month(tbl_manhours.Begin) as mymonth,year( tbl_manhours.Begin) as myyear, tbl_manhours.ParentCompany, Sum(tbl_manhours.Hours) AS SumOfHours, DSum("Hours","tbl_Manhours","month([Begin])<= " & mymonth & "and year(tbl_manhours.begin)=" & year(tbl_manhours.begin) & " AND begin between #" &  [Forms]![ Date Range]![Start Date] & " # And #" &  [Forms]![Date Range]![End Date] & "# AND ParentCompany='" & parentcompany & "'"  ) as RunningSum
FROM tbl_manhours
WHERE begin  Between [Forms]![ Date Range]![Start Date] And [Forms]![Date Range]![End Date]
GROUP BY   tbl_manhours.ParentCompany, year( tbl_manhours.Begin), month(tbl_manhours.Begin)

In the SQL above, I used the month() function in place of the format() function you used. Additionally since both month and year are reserved words in Access, you should not use them as field aliases. I used mymonth and myyear.

I've attached an example database.

 

[Rene Lazaro]

Good morning jzwp22,

This is it! Exactly what I needed!

Thanks a loooooottttttttt!

Rene

 

[jzwp22]

You're welcome! Glad to help out.

 

[Rene Lazaro]

I am trying to use the same approach with another scenario but am unsuccessful, can you help?

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] This is now a relationship to a Facility Table

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]

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

I am trying to use the same expression you provided for the previous Hours issue but I receive error when I run the query.

Can you help?

Thanks again,

Rene

 

[jzwp22]

What is the datatype of the Injury Classification field? It appears to be text. You cannot multiply a text field by -1 nor can you sum it. Are you after a count of how many records had an injury classification = "Lost Time"?
What are the fields in the injury table? Something like this:

tbl_injuries
-injury date
-Injury Classification
any other fields?

Can you provide the structure of the other tables involved (facility table, company table?)?

 

[Rene Lazaro]

[Injury Classification] is a text field.

Yes; I am after a count of how many records had an injury classification = "Lost Time".

tbl_Injuries
1) Case Number (Auto Number) - Primary Key
2) Social Security Number (Text)
3) Injury Date (Date/Time)
4) Description (Memo)
5) Insurance Code (Number)
6) Injury Classification (Text)
7) Injury Status (Text)

tbl_Facilities
1) Insurance Code (Number) - Primary Key
2) Facility (Text)
3) Division (Text)
4) Parent Company (Text)

The Injury Table is related to the Facility Table via the field called [Insurance Code]. The [Injury Classification] field in the Injury Table is a combo box who's source is the following table:

tbl_Injury Classifications
1) Injury Classification (Text)

Thanks!!

Rene

 

[jzwp22]

I have concerns regarding this statement:

The [Injury Classification] field in the Injury Table is a combo box who's source is the following table:

Although Access has the capability of lookups (combo & list boxes) at the table level; it is generally not recommended. See this link for an explanation.

I took the database I provided yesteday and modified it accordingly; it is attached.

I first created a query that got the data using the date range criteria & type of injury you were after (qryGetLostTimeInjuriesForAPeriod) . Then from there, I created another query to get the counts (qryGetCountsWithRunningCount). Then using a third query, I joined the second query back to the facilities table to get the Parent Company name (qryRunningCountwithParentCo). Running the last query, shows the data you were after.

 

[Rene Lazaro]

I was not aware of the evils of using lookup fields in tables.

This is perfect! Thanks again!!!!!!!!!!!

Rene

 

[jzwp22]

You're welcome

 

[spikemedic]

I am aggravating to use the aforementioned announcement you provided for the antecedent Hours affair but I accept absurdity if I run the query.



__________________
Individual health insurance

 

[jzwp22]

Could you explain further? Are you trying to adapt the query to your particular database? What does your table structure look like?

I have found that if your computer (not the date format in Access) is set up for a different date format such as commonly used in Europe, these types of queries do not work the same. I have not been able to duplicate the problem here.