?
Solved

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

Posted on 2004-08-31
6
Medium Priority
?
286 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:Trygve Thayer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 16

Accepted Solution

by:
Nestorio earned 2000 total points
ID: 11948555
Hi TThayer,

Have you tried this query? It is not the same you posted above.

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

Tell me if it works.

Regards
0
 
LVL 3

Author Comment

by:Trygve Thayer
ID: 11948591
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
0
 
LVL 16

Expert Comment

by:Nestorio
ID: 11948706
That's why [Ocurrence Value] is enclosed between brackets. It would work, though.

Good luck.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 3

Author Comment

by:Trygve Thayer
ID: 11948749
Thanks for your help.  I will try to work on getting rid of the $ signs but not sure where to start.
0
 
LVL 3

Author Comment

by:Trygve Thayer
ID: 11948763
Got it!  Changed the format on the report.
0
 
LVL 16

Expert Comment

by:Nestorio
ID: 11948814
Glad to have helped.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question