Match records surpressed

[IceRuby]

Using Crystal 11 with ODBC connection to SQL 2000 database

I need to write a report to compare values that have been entered via timesheet transactions (these held within timesheet header and time sheet line tables) and actual payroll transactions (held in payroll transaction table)

A common table - payroll record - provides a link between all tables but there is a miss match in the number of records held which causing duplicate records in a single report so my plan was to complete two reports with following details.

empid payroll end date tran date paycode hrs amount

The next part is the part I am unsure of.

I need to then have crystal match each record and only display those that do not match. e.g. Timesheet lines may have 5 records, payroll tranasctions only 4 records therefore the odd record is displayed.

Is a subreport the best means of getting to answer?

Appreciate your assistance.

 

[dgillz]

How would you determine the "odd" record? Exactly what constitues a "match"? Please provide specific data examples and desired output.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[lbass]

Is there a way you can link employee ID->employee ID, timesheet date->payroll end date using left joins FROM the timesheet table to the payroll table? I'm guessing you wouldn't have a payroll record without a timesheet, but that you might have a timesheet without a payroll record.

Then you might be able to use a record selection formula like:

isnull({payroll.payrollenddate})

-LB

 

[IceRuby]

Sorry I missed critical item in mismatch. Three potential mismatches. First - Paycode Second - Hrs (no.) Third- Tran date

eg. Missing Paycode and Hrs.

TIMESHEET
empid payroll end date tran date paycode hrs amount
65 21/06/05 23/6/05 OT20 5 $100.00
65 21/06/05 26/6/05 OT15 2 $20.00
65 25/06/05 26/6/05 OT20 3 $45.00

PAYROLL TRANSACTION
empid payroll end date tran date paycode hrs amount
65 21/06/05 23/6/05 OT20 5 $100.00
65 21/06/05 26/6/05 OT15 2 $20.00

The red line is the item I would like to report

 

[dgillz]

Still a little fuzzy, but I would link on empid, paycode, hrs and trandate. Is this correct?

If so, I do not like this link because what would happen if TIMESHEET records 1 and three were for the same amount of hours? You would have a many to one relationship. I suspect there is another database field that makes this a unique record which you need to include on your report and in your link. I suggest you research this.

At any rate, after you are happy with the link, write a record selection formula:

isnull({PAYROLL TRANSACTION.empid})

The actual field you use above really does not matter, as if the link is correct, ALL of the fields will be null.

I really think this link is likely to cause you trouble however for the reasons stated above.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz