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

[supportsvc]

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

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?

 

[dhookom]

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

 

[SkipVought]

Hi,

CalendarYear? Criteria for a Quarter?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[supportsvc]

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?

 

[Andrzejek]

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

Previous year's data would show if you do: [tt]WHERE PR_EmployeeTaxHistory.CalendarYear = Year(Date())[highlight #FCE94F] - 1[/highlight][/tt]


---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

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

 

[supportsvc]

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?

 

[dhookom]

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

 

[supportsvc]

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

Need to change

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

to something like this

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

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?

 

[dhookom]

Try:

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

 

[supportsvc]

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

 

[dhookom]

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

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

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

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

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

 

[supportsvc]

THANK YOU SO MUCH!