Display Checks without Deductions?

Hello,
I can't seem to figure out how to display ALL checks for the month regardless of having any deductions paid.

This is what I'm thinking?

({PR_PayrollHistoryHeader.CheckDate} in MonthToDate and

{PR_PayrollHistoryDetail.DeductionCode} in ["000004", "000013", "000020", "000023", "KU", "RU"] )

or

({PR_PayrollHistoryHeader.CheckDate} in MonthToDate and ISNULL({PR_PayrollHistoryDetail.DeductionCode}))

Expected result
CheckDate CheckNo Earnings   Deductions   Amt
1/5/2018 00001    $1500      401k        $10
1/5/2018 00002    $1000      401k        - or $0 (this would be NULL if no deductions were taken out)

NOTE: requester would like to have the Deductions display as columns (cross-tab) and show - or $0 when there were no deductions from a check.


Edited by techsupport - 23 Jan 2018 at 7:03am

Remember any time you mix AND's and OR's, be sure to put parens around the OR statements that belong together.  Otherwise you will get odd results.

The () around the ones that belong together and still does not result in the way expected.

So to make sure this is set up correctly.

You want the

{PR_PayrollHistoryHeader.CheckDate} in the month to date AND
{PR_PayrollHistoryDetail.DeductionCode} to be one of the following - "000004", "000013", "000020", "000023", "KU", "RU"
OR 
{PR_PayrollHistoryHeader.CheckDate}  in the month to date AND
{PR_PayrollHistoryDetail.DeductionCode} is NULL (be sure to have Default Value for Nulls selected in the formula editor.

Sometimes with strings, it seems to work better to do a len check
len({PR_PayrollHistoryDetail.DeductionCode}) = 0 (Thanks to Ken Hamady for that one)

Thanks

However, it's still not working

This is the SQL in Crystal Reports

 SELECT "PR_PayrollHistoryHeader"."CheckDate", "PR_PayrollHistoryHeader"."CheckNo", "PR_PayrollHistoryHeader"."TotalRegularHours", "PR_PayrollHistoryHeader"."TotalRegularEarnings", "PR_PayrollHistoryDetail"."DeductionCode", "PR_PayrollHistoryDetail"."PayAmt", "PR_PayrollHistoryHeader"."EmployeeKey"

 FROM   "PR_PayrollHistoryDetail" "PR_PayrollHistoryDetail", "PR_PayrollHistoryHeader" "PR_PayrollHistoryHeader"

 WHERE  ((("PR_PayrollHistoryDetail"."EmployeeKey"="PR_PayrollHistoryHeader"."EmployeeKey") AND ("PR_PayrollHistoryDetail"."CheckNo"="PR_PayrollHistoryHeader"."CheckNo")) AND ("PR_PayrollHistoryDetail"."HeaderSeqNo"="PR_PayrollHistoryHeader"."HeaderSeqNo")) AND ((("PR_PayrollHistoryDetail"."DeductionCode"='000004' OR "PR_PayrollHistoryDetail"."DeductionCode"='000013' OR "PR_PayrollHistoryDetail"."DeductionCode"='000020' OR "PR_PayrollHistoryDetail"."DeductionCode"='000023' OR "PR_PayrollHistoryDetail"."DeductionCode"='KU' OR "PR_PayrollHistoryDetail"."DeductionCode"='RU')) OR ("PR_PayrollHistoryDetail"."DeductionCode" IS  NULL )) AND ("PR_PayrollHistoryHeader"."CheckDate">={d '2018-01-01'} AND "PR_PayrollHistoryHeader"."CheckDate"<={d '2018-01-23'})

 ORDER BY "PR_PayrollHistoryHeader"."EmployeeKey", "PR_PayrollHistoryHeader"."CheckDate", "PR_PayrollHistoryHeader"."CheckNo"

This is the SQL in Access

SELECT PR_PayrollHistoryDetail.EmployeeKey, PR_PayrollHistoryHeader.CheckDate, PR_PayrollHistoryDetail.CheckNo, PR_PayrollHistoryHeader.TotalRegularHours, PR_PayrollHistoryHeader.TotalGrossEarnings, Sum(IIf([PR_PayrollHistoryDetail].[DeductionCode]="000004",IIf([PR_PayrollHistoryDetail].[DeductionCode] Is Null,0,[PayAmt]),0)) AS [Intl Pension], Sum(IIf([PR_PayrollHistoryDetail].[DeductionCode]="000013",IIf([PR_PayrollHistoryDetail].[DeductionCode] Is Null,0,[PayAmt]),0)) AS 401k, Sum(IIf([PR_PayrollHistoryDetail].[DeductionCode]="KU",IIf([PR_PayrollHistoryDetail].[DeductionCode] Is Null,0,[PayAmt]),0)) AS [401K Catchup], Sum(IIf([PR_PayrollHistoryDetail].[DeductionCode]="000020",IIf([PR_PayrollHistoryDetail].[DeductionCode] Is Null,0,[PayAmt]),0)) AS [Intl Loan- MO], Sum(IIf([PR_PayrollHistoryDetail].[DeductionCode]="RU",IIf([PR_PayrollHistoryDetail].[DeductionCode] Is Null,0,[PayAmt]),0)) AS [ROTH Catch-Up], Sum(IIf([PR_PayrollHistoryDetail].[DeductionCode]="000023",IIf([PR_PayrollHistoryDetail].[DeductionCode] Is Null,0,[PayAmt]),0)) AS [Roth IRA], Month([CheckDate]) AS [Month], Year([CheckDate]) AS [Year], IIf([PR_PayrollHistoryDetail].[DeductionCode] Is Null,0,[PayAmt]) AS Amt

FROM (PR_PayrollHistoryDetail INNER JOIN PR_PayrollHistoryHeader ON (PR_PayrollHistoryDetail.HeaderSeqNo = PR_PayrollHistoryHeader.HeaderSeqNo) AND (PR_PayrollHistoryDetail.CheckNo = PR_PayrollHistoryHeader.CheckNo) AND (PR_PayrollHistoryDetail.EmployeeKey = PR_PayrollHistoryHeader.EmployeeKey)) LEFT JOIN PR_DeductionCode ON PR_PayrollHistoryDetail.DeductionCode = PR_DeductionCode.DeductionCode

WHERE (((PR_PayrollHistoryDetail.DeductionCode) In ("000004","000013","000020","000023","RU","KU") Or (PR_PayrollHistoryDetail.DeductionCode) Is Null))

GROUP BY PR_PayrollHistoryDetail.EmployeeKey, PR_PayrollHistoryHeader.CheckDate, PR_PayrollHistoryDetail.CheckNo, PR_PayrollHistoryHeader.TotalRegularHours, PR_PayrollHistoryHeader.TotalGrossEarnings, Month([CheckDate]), Year([CheckDate])

HAVING (((PR_PayrollHistoryHeader.CheckDate) Between #1/1/2018# And #1/31/2018#)

ORDER BY PR_PayrollHistoryDetail.EmployeeKey, PR_PayrollHistoryHeader.CheckDate, PR_PayrollHistoryDetail.CheckNo;

NOTE: it works in Access but NOT in Crystal.

Does anyone know the SQL in Crystal so it works like the Access SQL? I have confirmed Access SQL works as expected.


Edited by techsupport - 23 Jan 2018 at 6:30pm

Not sure why you have a HAVING clause in the Access query. But I do not think it will affect anything. 

I have never tried to write a command for Access queries.  Have you tried dropping the Access query into a command and see if it works.   I know that all the CR fields will have to be replaced (you might be able to re-map them).

So back to the original question.  What is not working?

Finally got the NULL values to show. It was a placement issue. I had to place the IsNull first.

Now my issue is that in Access it does not duplicate the check details but it does in Crystal Reports.

Although I can suppress it, which I did, I am unable to get the correct SUM due to the details repeating information in Crystal Reports.

NOTE: it does not duplicate / repeat in Access.

Saw the post on another forum.  As they say 'Duh! I knew that'.

Have you tried selecting Distinct Records?  Not sure why it would not be duplicating in Access.

It is not duplicating in Access but it is in Crystal.

I know the Grouping is different as I have Group on all the fields in Access and SUM on the deduction columns.

Looks like you got the answer you needed on another forum.  Hope it all works for you now.