Access SQL to Crystal Report Writer SQL 1

[supportsvc]

Hello,
So I was able create a query in order to get the expected results.
However when trying to do the same in Crystal Reports, it's not producing the same results.

Access DOES show ALL checks regardless of DeductionCode on check
CRW is NOT showing some checks without DeductionCode on check

In Access I have:

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]), IIf([PR_PayrollHistoryDetail].[DeductionCode] Is Null,0,[PayAmt])
HAVING (((PR_PayrollHistoryDetail.EmployeeKey)="3530074722614606B871A5148B7E0730") AND ((PR_PayrollHistoryHeader.CheckDate) Between #1/1/2018# And #1/31/2018#) AND ((Month([CheckDate]))=Month(Date())) AND ((Year([CheckDate]))=Year(Date())))
ORDER BY PR_PayrollHistoryDetail.EmployeeKey, PR_PayrollHistoryHeader.CheckDate, PR_PayrollHistoryDetail.CheckNo;

In Crystal Reports it shows as

 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_PayrollHistoryHeader"."CheckDate">={d '2018-01-01'} AND "PR_PayrollHistoryHeader"."CheckDate"<={d '2018-01-31'}) 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 ))
 ORDER BY "PR_PayrollHistoryHeader"."EmployeeKey", "PR_PayrollHistoryHeader"."CheckNo", "PR_PayrollHistoryHeader"."CheckDate"

What do I need to change on CRW to result the same as it does in Access?

 

[lbass]

You have to do the null check the first time you reference the deduction code field. Move the last line to the beginning of the "or" statement that references that field in the where clause.

-LB

 

[supportsvc]

Wow! Thank you!
I thought I tried placement, however changing it so the IsNull is first on the DeductionCode formula, I am getting all the NULLs now.

However, it is repeating / duplicating details of the same check. Not sure how to get the correct SUM with the details repeating? I can suppress it from showing but not when trying to SUM the HOURS and EARNINGS.

I have Groups on
1) EmployeeKey
2) CheckDate
3) CheckNo

so that i can sum the deduction codes, however, as you can see the Hours and Earnings are repeated and can't sum them as that would be an incorrect sum.

This is the updated version

 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" IS  NULL ) OR (("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'))) AND ("PR_PayrollHistoryHeader"."CheckDate">={d '2018-01-01'} AND "PR_PayrollHistoryHeader"."CheckDate"<={d '2018-01-31'})
 ORDER BY "PR_PayrollHistoryHeader"."EmployeeKey", "PR_PayrollHistoryHeader"."CheckDate", "PR_PayrollHistoryHeader"."CheckNo"

 

[lbass]

You can use running totals where you evaluate on change of group (Check no). The results will be correct in footer sections. If you need the sums per some outer group, then add a reset on change of that group.

-LB

 

[supportsvc]

Ok, thank you!

It's looking like it's totaling as expected.

 

[supportsvc]

ok so another issue on this same report ...

As you saw I have formulas for each deduction code so they display as columns.
And the deductions codes are "hard-coded". The end-user needs the ability to select other deduction codes.

NOTE: it needs to display NULL deduction no matter what the user selects.

Not sure how to handle that and still display selected options as columns?

 

[lbass]

For future reference, you should be starting a new thread for each new topic. Please see thread149-655589 for help with creating dynamic columns.

-LB

 

[lbass]

PS. Your record selection formula should look like this:

(
Isnull({table.field}) or
{table.field} in {?YourParameter}
)

 

[supportsvc]

Gotcha

And thank you! Will try them