×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Previous Quarter Sum and Last Quarter Sum in 1st Q the Following Year

Previous Quarter Sum and Last Quarter Sum in 1st Q the Following Year

Previous Quarter Sum and Last Quarter Sum in 1st Q the Following Year

(OP)
Hello,
So I'm close but can't figure out how to go about only showing the previous quarter sum and then to handle the last quarter to display when ran in the 1st quarter the following year.

This is what I have so far

CODE

SELECT PR_Employee.EmployeeKey, PR_Employee.DepartmentNo, PR_Employee.EmployeeNo, PR_Employee.LastName, PR_Employee.FirstName, PR_Employee.SocialSecurityNo, PR_EmployeeTaxFilingStatus.TaxGroup, PR_EmployeeTaxFilingStatus.FilingStatus, PR_EmployeeTaxFilingStatus.TaxCalculationOverrideCode, Sum(IIf([CalendarMonth] In ("01","02","03"),[WagesSubjToWithholding],0)) AS Q1, Sum(IIf([CalendarMonth] In ("04","05","06"),[WagesSubjToWithholding],0)) AS Q2, Sum(IIf([CalendarMonth] In ("07","08","09"),[WagesSubjToWithholding],0)) AS Q3, Sum(IIf([CalendarMonth] In ("10","11","12"),[WagesSubjToWithholding],0)) AS Q4, (Val([CalendarMonth])+2)\3 AS Quarter
FROM PR_Employee INNER JOIN (PR_EmployeeTaxHistory INNER JOIN PR_EmployeeTaxFilingStatus ON (PR_EmployeeTaxFilingStatus.TaxGroup = PR_EmployeeTaxHistory.TaxGroup) AND (PR_EmployeeTaxHistory.EmployeeKey = PR_EmployeeTaxFilingStatus.EmployeeKey)) ON (PR_Employee.EmployeeKey = PR_EmployeeTaxHistory.EmployeeKey) AND (PR_Employee.EmployeeKey = PR_EmployeeTaxFilingStatus.EmployeeKey)
WHERE (((PR_EmployeeTaxHistory.CalendarYear)=Year(Date())))
GROUP BY PR_Employee.EmployeeKey, PR_Employee.DepartmentNo, PR_Employee.EmployeeNo, PR_Employee.LastName, PR_Employee.FirstName, PR_Employee.SocialSecurityNo, PR_EmployeeTaxFilingStatus.TaxGroup, PR_EmployeeTaxFilingStatus.FilingStatus, PR_EmployeeTaxFilingStatus.TaxCalculationOverrideCode, (Val([CalendarMonth])+2)\3
HAVING (((PR_EmployeeTaxFilingStatus.TaxGroup)<>"FEDERAL")); 

What and do I change so the Sum is always the Previous Quarter?

RE: Previous Quarter Sum and Last Quarter Sum in 1st Q the Following Year

You have 4 quarterly sums. What date quarters would you expect to see in March or April of 2020? I am looking for answers like Q1 2020 or Q4 2019.

I'm not sure why you are using the IIf() to generate the quarter when it seems you have figured out how to use (Val([CalendarMonth])+2)\3 AS Quarter.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Previous Quarter Sum and Last Quarter Sum in 1st Q the Following Year

Hi,

CalendarYear? Criteria for a Quarter?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Previous Quarter Sum and Last Quarter Sum in 1st Q the Following Year

(OP)
Dhookom,
The Datepart("q",Date()) doesn't give me previous quarter under (Val([CalendarMonth])+2)\3

Datepart("q",Date()-1) does

Does this also accomplish 4th quarter when running the query in the 1st quarter of next year?

RE: Previous Quarter Sum and Last Quarter Sum in 1st Q the Following Year

>handle the last quarter to display when ran in the 1st quarter the following year.
You ask for the data WHERE PR_EmployeeTaxHistory.CalendarYear = Year(Date()) which is the current Year ONLY.

Previous year's data would show if you do: WHERE PR_EmployeeTaxHistory.CalendarYear = Year(Date()) - 1


---- Andy

There is a great need for a sarcasm font.

RE: Previous Quarter Sum and Last Quarter Sum in 1st Q the Following Year

supportsvc,
So the only time you want to see a fourth quarter from the previous year is during the first quarter of a new year? During the 2nd - 4th quarters of a year you will only see data from the current year?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Previous Quarter Sum and Last Quarter Sum in 1st Q the Following Year

(OP)
Andrzejek,
Yes, I know that much. It's about handling the next year and displaying 4th quarter.


dhookom,
Correct

And for the sum of the previous quarter, not sure how to incorporate the quarter criteria with the sum?

RE: Previous Quarter Sum and Last Quarter Sum in 1st Q the Following Year

You have values for CalendarYear (numerid) and CalendarMonth (text).

Is the objective to change the WHERE clause from:
WHERE PR_EmployeeTaxHistory.CalendarYear=Year(Date())
to
if current quarter is 1 then change the where condition to include the previous year quarter 4.

Can we assume there is no future data or WagesSubjToWithholding recorded against future months?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Previous Quarter Sum and Last Quarter Sum in 1st Q the Following Year

(OP)
dhookom,
Hello, sorry I'm having difficulty trying to explain what I'm looking for / needing

Need to change

CODE

Sum(IIf([CalendarMonth] In ("01","02","03"),[WagesSubjToWithholding],0)) AS Q1, etc .... through Q4 

to something like this

CODE

Sum(IIf(Val([CalendarMonth])+2)\3 = Datepart("q",Date()-1),[WagesSubjToWithholding],0)) AS PrevQtr 

and getting rid of the Q1, Q2, Q3, Q4

so that every quarter it'll show the previous quarter and handle showing the 4th quarter in the following 1st quarter
to your

Quote:

the only time you want to see a fourth quarter from the previous year is during the first quarter of a new year? During the 2nd - 4th quarters of a year you will only see data from the current year?

RE: Previous Quarter Sum and Last Quarter Sum in 1st Q the Following Year

Try:

CODE --> SQL

Sum(Abs( Format(DateAdd("q",-1,Date()),"yyyyq") = Format(DateSerial([CalendarYear],[CalendarMonth],1),"yyyyq")) * [WagesSubjToWithholding]) AS PrevQtr 


You may need to explicit convert [CalendarMonth] to numeric.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Previous Quarter Sum and Last Quarter Sum in 1st Q the Following Year

(OP)
THANK YOU!
It's showing 3rd quarter.
Not sure how to check to see if 4th quarter will show in the new year?

RE: Previous Quarter Sum and Last Quarter Sum in 1st Q the Following Year

Do what I do and use the immediate/debug window. Press Ctrl+G and enter

CODE --> debug

? Format(DateAdd("q",-1,Date()),"yyyyq") 

now replace Date() with any date to check the results

CODE --> debug

?  Format(DateAdd("q",-1,#2/2/2019#),"yyyyq") 

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Previous Quarter Sum and Last Quarter Sum in 1st Q the Following Year

(OP)
THANK YOU SO MUCH!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close