asked on

Report to count up total occurrences by Employee from current quarter back 7 quarters.

Objective
I am trying to create a report to show an employee's total occurrences for the current quarter back 7 quarters. I
have had a lot of help but not quite there yet. Where I am at is creating a query to get the information.

Rules to Calculate are below

A quarter is a set date during a year. The quarter ends March 31, June 30, September 30, and December 31 for
any given year.

An employee's totals at the end of a quarter needs to be checked from the ending quarter back one year.

Example
If I put in a quarter ending date of 6/30/04 the calculation for that period would be to start with that date and go
back 364 days and total up the employee's occurrences. Then I would bounce back to the prior quarter of 3/31/04
and count back 364 days again until I have a total of 8 quarters.

Currently here is the code I think I am close with. In the table tblOccurrence the date field is OccurrenceDate and the value I want to add up is in a field called OccurrenceValue. I like this code because it asks me for a date to start with.

SELECT Employee,
sum(iif(datediff("m", [OccurrenceDate] - 1, dateadd("q", -7, dtRefDate)) between 1 and 12, 1, 0)) as [Quarter1],
sum(iif(datediff("m", [OccurrenceDate] - 1, dateadd("q", -6, dtRefDate)) between 1 and 12, 1, 0)) as [Quarter2],
sum(iif(datediff("m", [OccurrenceDate] - 1, dateadd("q", -5, dtRefDate)) between 1 and 12, 1, 0)) as [Quarter3],
sum(iif(datediff("m", [OccurrenceDate] - 1, dateadd("q", -4, dtRefDate)) between 1 and 12, 1, 0)) as [Quarter4],
sum(iif(datediff("m", [OccurrenceDate] - 1, dateadd("q", -3, dtRefDate)) between 1 and 12, 1, 0)) as [Quarter5],
sum(iif(datediff("m", [OccurrenceDate] - 1, dateadd("q", -2, dtRefDate)) between 1 and 12, 1, 0)) as [Quarter6],
sum(iif(datediff("m", [OccurrenceDate] - 1, dateadd("q", -1, dtRefDate)) between 1 and 12, 1, 0)) as [Quarter7],
sum(iif(datediff("m", [OccurrenceDate] - 1, dtRefDate) between 1 and 12, 1, 0)) as [Quarter8]
from tblOccurrence
group by Employee

ASKER CERTIFIED SOLUTION

Nestorio

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Trygve Thayer

ASKER

Yee Ha! Hoping you were out there. I did try the other query but it asks for both a dtrefDate and and Occurrence Value. I am putting in 6/30/03 for the date but dont know what to put in for the value. While typing this I just had a thought to check. I have changed the Occurrence Date to one word OccurrenceDate and the Occurrence Value to One word OccurrenceValue. I made the adjustments and it worked. All the values came up as currency as I am using currency to get decimal values to show up on other forms. Is there a way to just show numbers to 2 decimal places without the $ signs?

Here is what I got the numbers are close to what I manually calculated but then I could have made some mistakes. Numbers run together for me.

Employee      Quarter1      Quarter2      Quarter3      Quarter4      Quarter5      Quarter6      Quarter7      Quarter8
Ausmus, Adam      $0.00      $0.00      $0.00      $0.00      $0.00      $0.00      $0.00      $1.00
Thayer, Trygve      $4.00      $4.00      $5.00      $9.00      $10.00      $10.00      $9.00      $4.00

Nestorio

That's why [Ocurrence Value] is enclosed between brackets. It would work, though.

Good luck.

Trygve Thayer

ASKER

Thanks for your help. I will try to work on getting rid of the $ signs but not sure where to start.

Trygve Thayer

ASKER

Got it! Changed the format on the report.

Nestorio

Glad to have helped.