Convert Access SQL Statement to SQL in Crystal Reports?

system · January 24, 2018, 6:56am

My SQL Statement in Access produces the results sought.
When attempting to do the same in Crystal Reports, I do not get all the records as I do in Access nor the same details.

Not sure how to modify it so Crystal Reports produces the same results.

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]

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 ((Month(PR_PayrollHistoryHeader.CheckDate)=Month(Date()) and Year(PR_PayrollHistoryHeader.CheckDate)=Year(Date())

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

Desired result I am getting in Access is:
All checks, regardless of any deductions paid, to display and place $0 under the deduction if check did not have any deductions.[/code]

techsupport15 (BOB member since 2015-10-05)

system · January 24, 2018, 9:44pm

Did you use this in a Command in Crystal or are you just linking tables together in the report?

-Dell

hilfy (BOB member since 2007-04-16)

system · January 24, 2018, 11:02pm

No, it doesn’t work in Crystal Reports.

This is what the linked tables with formulas in CRW looks like in SQL that I was mimicking from Access

 SELECT "PR_PayrollHistoryHeader"."CheckDate", "PR_PayrollHistoryHeader"."CheckNo", "PR_PayrollHistoryHeader"."TotalRegularHours", "PR_PayrollHistoryHeader"."TotalRegularEarnings", "PR_PayrollHistoryDetail"."DeductionCode", "PR_PayrollHistoryDetail"."PayAmt", "PR_EmployeePIIWrk"."LastName", "PR_EmployeePIIWrk"."FirstName", "PR_EmployeePIIWrk"."DepartmentNo", "PR_EmployeePIIWrk"."EmployeeNo", "PR_PayrollHistoryHeader"."HeaderSeqNo"
 FROM   "PR_PayrollHistoryDetail" "PR_PayrollHistoryDetail", "PR_PayrollHistoryHeader" "PR_PayrollHistoryHeader", "PR_EmployeePIIWrk" "PR_EmployeePIIWrk"
 WHERE  ((("PR_PayrollHistoryDetail"."EmployeeKey"="PR_PayrollHistoryHeader"."EmployeeKey") AND ("PR_PayrollHistoryDetail"."CheckNo"="PR_PayrollHistoryHeader"."CheckNo")) AND ("PR_PayrollHistoryDetail"."HeaderSeqNo"="PR_PayrollHistoryHeader"."HeaderSeqNo")) AND (("PR_PayrollHistoryDetail"."EmployeeKey"="PR_EmployeePIIWrk"."EmployeeKey") AND ("PR_PayrollHistoryHeader"."EmployeeKey"="PR_EmployeePIIWrk"."EmployeeKey")) 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-05'} AND "PR_PayrollHistoryHeader"."CheckDate"<={d '2018-01-26'})

[/code]

techsupport15 (BOB member since 2015-10-05)

system · January 25, 2018, 2:38pm

It’s duplicating because the query is bringing back multiple rows of data for each check. What you’ll need to do is something like this:

Group the report by Employee Number, Check Date, and Check No - you’ll need all three to get the data sorted correctly.
Suppress the following sections: Employee Number header and footer, Check Date header and footer, Details, and Check No header. You’ll put all of your data in the Check No footer.
Instead of just putting the data fields for the summaries from the original query, you’re going to use running totals to get the value per check, something like this:

Running Total Name: Intl Pension
Field to Summarize: {PR_PayollHistoryDetail.PayAmt}
Type of Summary: Sum
Evaluate: Use a formula:
{PR_PayrollHistoryDetail.DeductionCode}=“000004”
Reset: On change of group {PR_PayrollHistoryHeader.CheckNo}

You’ll copy this pattern for each of the sums from your original query. Then you’ll put the data, using the running totals for the amounts, in the Check No Group Footer.

-Dell

hilfy (BOB member since 2007-04-16)

system · January 25, 2018, 2:50pm

Thank you!

Got it working!
Not used to the Running Totals!

techsupport15 (BOB member since 2015-10-05)