×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Access SQL to Crystal Report Writer SQL

Access SQL to Crystal Report Writer SQL

Access SQL to Crystal Report Writer SQL

(OP)
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:

CODE

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

CODE

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?

RE: Access SQL to Crystal Report Writer SQL

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

RE: Access SQL to Crystal Report Writer SQL

(OP)
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

CODE

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" 

RE: Access SQL to Crystal Report Writer SQL

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

RE: Access SQL to Crystal Report Writer SQL

(OP)
Ok, thank you!

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

RE: Access SQL to Crystal Report Writer SQL

(OP)
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?

RE: Access SQL to Crystal Report Writer SQL

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

-LB

RE: Access SQL to Crystal Report Writer SQL

PS. Your record selection formula should look like this:

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

RE: Access SQL to Crystal Report Writer SQL

(OP)
Gotcha

And thank you! Will try them

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close