Query to Display $0 if no record in Quarter with YTD total

[supportsvc]

I can't figure out how to display records with $0 but with YTD totals when there's no data in the selected quarter

Here's the query
The form has 3 for the Quarter and 2020 for the Year

Since the end goal is to report on all companies and employees of the reporting year on a quarterly basis, when a companies and employees don't have any data for a quarter, it doesn't show any data when a criteria is set for the Quarter in the query. Having removed it, it shows all quarters of the reporting quarter.

Example: Need to report on quarter 3 of wages paid in quarter 3 PLUS the YTD wages paid by ALL Companies and Employees.
Having the criteria to show the quarter 3 totals excludes YTD data for companies that didn't have any wages in quarter 3. But the report needs to show ALL companies and employees of the reporting year.

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 PreQtr, Sum(IIf(DatePart("q",[CheckDate])=[Forms]![frmProcessing]![CurrentQuarter],[WagesSubjToEmployerUI],0)) AS PrevQtr, Year([CheckDate]) AS [Year], DatePart("q",[CheckDate]) AS Quarter, tblCompanies.ProcessIt
FROM (tblEmployees INNER JOIN tblCheckHistory ON (tblEmployees.DepartmentNo = tblCheckHistory.DepartmentNo) AND (tblEmployees.EmployeeNo = tblCheckHistory.EmployeeNo) AND (tblEmployees.CompanyCode = tblCheckHistory.CompanyCode)) INNER JOIN tblCompanies ON (tblCheckHistory.CompanyCode = tblCompanies.DRCode) AND (tblEmployees.CompanyCode = tblCompanies.DRCode) AND (tblCheckHistory.CompanyCode = tblCompanies.DRCode)
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]), tblCompanies.ProcessIt
HAVING (((Year([CheckDate]))=[Forms]![frmProcessing]![CurrentYear]) AND ((tblCompanies.ProcessIt)=True));

see this
CompanyCode = 288 has data in Quarters 1 and 2 but not in 3 so with the criteria to report on Quarter 3 data, this company's YTD won't show.
Hopefully this made sense and someone can help me.

 

[SkipVought]

Hi,

Guess what? Your report doesn't have data for quarter 4 either!

If there's no data, there's no data!

You'd hafta have records in your table for each employee for each month that you'd wanna report with ZEROs.

Your problem is the data, not the report. Why isn't there data in the table for that company?

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]

Right, so was trying to see if and how to create a $0 record for those in Access because there is no data from the database

 

[SkipVought]

I'd CREATE it in Excel using MS Query to query your Access db.

Pretty simple to create a query to get a unique list CompanyCode & EmployeeID where no values exist for the specified months.

The from that list of CompanyCode & EmployeeID, create a column for date and one for payment, which would all be ZERO.

Then IMPORT the Excel table into Access and do a Table Update.

But maybe someone else has a better method.

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]

That is one way. Thanks

was hoping VBScript or query statement could do that.

Yes, if someone has a better suggestion, please share.
Thank you!

 

[dhookom]

If you want to show data for every employee for every quarter you start by creating a cartesian query of unique employees and quarters.

qcarEmpQtr

SELECT DISTINCT tblEmployees.EmployeeNum, Year([CheckDate]), DatePart("q",[CheckDate])
FROM tblEmployees, tblCheckHistory

Then you add this query into another query with JOIN lines that include all of the records from qcarEmpQtr.

If you filter by the CheckDate you would need to add "OR CheckDate Is Null"



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

 

[Andrzejek]

You could also create a simple table with Years and Quarters and join it with your other data. This way, even if you do not have any data for particular Year/Quarter, you will end up with NULLs which you can easily replace with 0's (NZ function). That will also eliminate hard-coded values in your original Select

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[supportsvc]

Thank you all! I requested that they just create a $0 record in the database to keep things consistent coming from the database vs doing all that setup in Access side.

 

[dhookom]

Ouch

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

 

[supportsvc]

Ok, so was able to show 0 but the count of employees are incorrect

The highlighted produced the 0 for when there isn't a check in given date parameter

SELECT DISTINCT tblCompanies.DRCode, [highlight]Sum(IIf(Nz([WagesSubjToEmployerUI],0)>0,1,0))[/highlight] AS CountOfEmployeeNo, Month([CheckDate]) AS [Month], Year([CheckDate]) AS [Year], DatePart("q",[CheckDate]) AS Qtr
FROM tblCheckHistory RIGHT JOIN tblCompanies ON tblCheckHistory.CompanyCode = tblCompanies.DRCode
WHERE (((tblCompanies.ProcessIt)=True))
GROUP BY tblCompanies.DRCode, Month([CheckDate]), Year([CheckDate]), DatePart("q",[CheckDate])
HAVING (((tblCompanies.DRCode)="212") AND ((Year([CheckDate]))=[Forms]![frmProcessing]![CurrentYear]))
ORDER BY tblCompanies.DRCode, Month([CheckDate]);

However the count is counting checks and not employees

How do I get the number of employees paid in each month and each quarter?

Details:

There are only 2 employees in each quarter; 1, 2 and 3

 

[SkipVought]

Seems you're moving the goal posts! Count employees or count checks: two separate CATEGORIES!

Using the results of a report (in this case a query) rather than going back to the source data with correct requirements, can be a significant problem--ipso facto!

You need correct DATA in order to aggregate. Nothing yields nothing.

And your latest, querying a transform, is yet another sign of a disaster--using a report (query) as the source of a report (query).

You might need something like
Count of employees paid
Count of employees not paid

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]

@Skip,
There is data. They are entering $0 for the quarter
Issue is it should display a count of 0 for the $0 employee record

 

[dhookom]

That's why I said "ouch" when you stated "create a $0 record in the database".

You seem to have totally avoided suggestions from Andy and me.

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

 

[supportsvc]

@Duane,
i'm sorry, i wasn't avoiding it. I had noted that's what I thought would have to be done however they are adding $0 records so the quarters will have the $0 record. Which resolves having to do all that in Access. Yes, data should come from the source.
Access is being used in order to create summary tax files for the State tax reporting.

As shown in the last post, it's counting number of checks and not number of employees in this query though the highlighted part is resolving the $0 record as 0 count but not the other records with >$0 is not a distinct count of employees.

SELECT DISTINCT tblCompanies.DRCode, [highlight #FCE94F]Sum(IIf(Nz([WagesSubjToEmployerUI],0)>0,1,0)) AS CountOfEmployeeNo[/highlight], Month([CheckDate]) AS [Month], Year([CheckDate]) AS [Year], DatePart("q",[CheckDate]) AS Qtr
FROM tblCheckHistory RIGHT JOIN tblCompanies ON tblCheckHistory.CompanyCode = tblCompanies.DRCode
WHERE (((tblCompanies.ProcessIt)=True))
GROUP BY tblCompanies.DRCode, Month([CheckDate]), Year([CheckDate]), DatePart("q",[CheckDate])
HAVING (((tblCompanies.DRCode)="068" Or (tblCompanies.DRCode)="212") AND ((Year([CheckDate]))=[Forms]![frmProcessing]![CurrentYear]))
ORDER BY tblCompanies.DRCode, Month([CheckDate]);

company 212 has only 2 employees each quarter. this is counting the number of checks and not distinct count of employees

How can this query or another one if needed, count the number of unique / distinct employees and not the number of checks?

Company 068 showing 0 in Mth1, Mth2, Mth3 in the Quarters are correct
Company 212 should show, as can be seen in the more detailed image, 2 distinct employees in the months/quarters.


Hope that made sense?

 

[supportsvc]

I think this may work

SELECT qryCompanyCount.DRCode, Sum(IIf(Nz([CountOfEmployeeNo],0)>0,1,0)) AS NoOfEmployeeNo, qryCompanyCount.Month, qryCompanyCount.Year, qryCompanyCount.Qtr
FROM qryCompanyCount
GROUP BY qryCompanyCount.DRCode, qryCompanyCount.Month, qryCompanyCount.Year, qryCompanyCount.Qtr
HAVING (((qryCompanyCount.DRCode)="068" Or (qryCompanyCount.DRCode)="212" Or (qryCompanyCount.DRCode)="288"));