Quarterly Query To Include YTD Totals

[supportsvc]

I can't figure out how to display the YTD totals with the Mth1, Mth2, Mth3 to display the previous quarter totals

This is what I have so far

SELECT tblEmployees.CompanyCode, tblEmployees.DepartmentNo, tblEmployees.EmployeeNo, tblEmployees.LastName, tblEmployees.FirstName, tblEmployees.SocialSecurityNo, tblCheckHistory.TaxGroup, tblEmployees.ResidenceTaxStateCode, tblCheckHistory.FilingStatus, Sum(IIf(((Month([CheckDate])-1) Mod 3)+1=1,[WagesSubjToEmployerUI],0)) AS Mth1, Sum(IIf(((Month([CheckDate])-1) Mod 3)+1=2,[WagesSubjToEmployerUI],0)) AS Mth2, Sum(IIf(((Month([CheckDate])-1) Mod 3)+1=3,[WagesSubjToEmployerUI],0)) AS Mth3, Sum(IIf(Month([CheckDate]) In (1,2,3),[WagesSubjToEmployerUI],0)) AS Q1, Sum(IIf(Month([CheckDate]) In (4,5,6),[WagesSubjToEmployerUI],0)) AS Q2, Sum(IIf(Month([CheckDate]) In (7,8,9),[WagesSubjToEmployerUI],0)) AS Q3, Sum(IIf(Month([CheckDate]) In (10,11,12),[WagesSubjToEmployerUI],0)) AS Q4, Sum(IIf(Month([CheckDate]) In (1,2,3,4,5,6,7,8,9,10,11,12),[WagesSubjToEmployerUI],0)) AS YTD, Sum(Abs(Format(DateAdd("q",-1,Date()),"yyyyq")=Format(DateSerial(Year([CheckDate]),Month([CheckDate]),1),"yyyyq"))*[WagesSubjToEmployerUI]) AS PrevQtr, Year([CheckDate]) AS [Year], DatePart("q",[CheckDate]) AS Quarter
FROM tblEmployees INNER JOIN tblCheckHistory ON (tblEmployees.CompanyCode = tblCheckHistory.CompanyCode) AND (tblEmployees.EmployeeNo = tblCheckHistory.EmployeeNo) AND (tblEmployees.DepartmentNo = tblCheckHistory.DepartmentNo)
WHERE (((tblCheckHistory.TaxGroup)=[Forms]![frmProcessing]![StateForProc]) AND ((tblCheckHistory.ResidenceTaxStateCode)=[Forms]![frmProcessing]![StateForProc]))
GROUP BY tblEmployees.CompanyCode, tblEmployees.DepartmentNo, tblEmployees.EmployeeNo, tblEmployees.LastName, tblEmployees.FirstName, tblEmployees.SocialSecurityNo, tblCheckHistory.TaxGroup, tblEmployees.ResidenceTaxStateCode, tblCheckHistory.FilingStatus, Year([CheckDate]), DatePart("q",[CheckDate])
HAVING (((Year([CheckDate]))=[Forms]![frmProcessing]![CurrentYear]));

This produces:

CompanyCode Mth1 Mth2 Mth3 Q1 Q2 Q3 Q4 YTD PrevQtr Year Quarter
054 $4,166.68 $4,166.68 $4,166.68 $12,500.04 $0.00 $0.00 $0.00 $12,500.04 $0.00 2020 1
054 $4,166.68 $4,166.68 $4,166.68 $0.00 $12,500.04 $0.00 $0.00 $12,500.04 $12,500.04 2020 2

Instead of multiple rows, how do you display Mth1-Mth3 for the previous quarter and the YTD? Where they need to report the previous quarter totals with the YTD totals

Did that make sense?

 

[dhookom]

I'm not sure what your months are (Month([CheckDate])-1) Mod 3)+1) but I would expect you might be best off creating a separate total query (or queries) to split up and simplify the calculations. Then create a select query of the previous totals queries for your final result.

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

 

[Andrzejek]

Also, it would be nice to know how your outcome should look like.
For example, how would you like to resolve [red]the last field[/red]:

[pre]
CompanyCode ... Quarter
054 ... [red] 1[/red]
054 ... [red] 2[/red]
[/pre]


---- Andy

There is a great need for a sarcasm font.

 

[supportsvc]

dhookom,
I was trying to avoid that if possible ...
Those are to report each month of the quarter.


Andrzejek,
As you saw, the last column Quarter is why it shows multiple rows
Year doesn't have to display, just need to ensure it's the reporting year but when Q4 will report, it reports in the following year. This is due to always reporting previous quarter, when the quarter is completed.

So instead is there a way to result in this format?

CompanyCode Mth1 Mth2 Mth3 Q1 Q2 Q3 Q4 YTD PrevQtr Year
054 $4,166.68 $4,166.68 $4,166.68 $12,500.04 $12,500.04 $0.00 $0.00 $23,000.08 $12,500.04 2020

Where the Mth1-Mth3 reports previous quarter's 3 months (just happens that the amounts for Q2 are the same as they were in Q1.

However the YTD should be the total of Q1 and Q2 and so on as the next quarters complete.

Hope this makes sense.

 

[dhookom]

You should use the Pre TGML tag to show your desired output.

So Mth1-Mth3 should be first quarter months the current date is second quarter, etc and fourth quarter when it is first quarter and the sum of these three Mth columns should equal PrevQtr?

Also, you apparently don't want to see any monthly totals other than the three months from the previous quarter?

You want this all in one row with no quarter column.

If YTD is the total for the current year, then I'm not sure why you are using

Sum(IIf(Month([CheckDate]) In (1,2,3,4,5,6,7,8,9,10,11,12),[WagesSubjToEmployerUI],0)) AS YTD,

when it is simply

Sum([WagesSubjToEmployerUI]) AS YTD,

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

 

[dhookom]

Also the criteria:

HAVING (((Year([CheckDate]))=[Forms]![frmProcessing]![CurrentYear]));

will not allow the display of any values from the previous year so there will be previous quarter during the months of January - March.

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

 

[supportsvc]

Yes! And got it! Thank you!
Sorry for the delay!

 

[supportsvc]

Correct, the user will be changing it to the prior year and the prior quarter

Also the criteria:
CODE --> SQL
HAVING (((Year([CheckDate]))=[Forms]![frmProcessing]![CurrentYear]));

will not allow the display of any values from the previous year so there will be previous quarter during the months of January - March.
Duane

In fact, I went ahead and changed it for user input on most all of the queries and reports.

Only one is the original query in this post

Mth1-Mth3 should be first quarter months the current date is second quarter, etc and fourth quarter when it is first quarter and the sum of these three Mth columns should equal PrevQtr?

Yes but need the YTD totals as well

https://files.engineering.com/getfi...e054a5a&file=Annotation_2020-08-12_191600.jpg