Previous Quarter Reporting Based on CalenderMonth

[aellistechsupport]

Hello,
I am trying to sum WagesSubjToWitholding for the previous quarter.
So, run the previous quarter every quarter of the calendar year.

Here's a snippet

 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

WHERE (((PR_EmployeeTaxHistory.CalendarYear)=Year(Date())))

I am not sure how to use

(Val([CalendarMonth])+2)\3

due to the only fields in the table are CalendarYear and CalendarMonth, no actual Date field.

What is the syntax to Sum the previous quarter only and not separately with Q1, Q2, Q3, Q4?

And the 4th quarter needs to report at the beginning of next year.
So the 4th quarters; CalendarMonth IN ("10","11",12") would show AS PrevQtr in the first quarter in 2020



Hopefully I made sense?

[ranman256]

I have a form with txtStart,and txtEnd.
user can free form pick the dates or select combo boxes,like Quarters, month.
This will fill in the 2 boxes.

Q1 uses these :
select * from table where [dateFld] between forms!myForm!txtstart and forms!myForm!txtEnd.

there are another 2 text boxes....txtStartPrev and txtEndPrev.
Q2 pulls the previous dates of the ones in Q1. Be it day,month,quarter.

[aellistechsupport]

Thank you however this needs to be without user input

Found this

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

Seems to be working. Not sure how to test the 4th quarter in the next year though but looks like it should work.

[ArviLaanemets]

The formula to calculate previous quarter in format yyyyq:

PreviousQuarter = =100*([CalendarYear]+(INT(([CalendarMonth]-1)/3)=0))+INT((CalendarMonth]-1)/3)-(INT(([CalendarMonth]-1)/3)=0)*4

From there on it is easy!
When calculating form control value, you use DSum() with formula for previous quarter as parameter;
When creating an aggregate query, you calculate PreviousQuarter field using the formula, and group by same formula.