Removing inflated data

[mdj3366]

Hi There,
I am using CR 11, SQL database.
I have a table that stores payment (payment) information and another that stores call information (calllog). I have a report that displays when a payment has actually been processed, using info. from the payment table. When I try to add the check date that is listed in the calllog table to the report, I am getting inflated data. The report is returning the same check date multiple times when more than one payment has been process during the calllog check date.
This is an example of how I want the data returned:
Name payment_date wages UI payment_amt status Date Processed Check Date
IL 06/12/2009 2,384.62 0.00 2,384.62 Paid 6/15/2009 5:57:35 PM 6/22/2009
IL 06/19/2009 2,980.77 0.00 2,980.77 Eligible 6/15/2009 4:02:58 PM 6/22/2009
IL 06/26/2009 2,980.77 0.00 2,980.77 Eligible 6/22/2009 5:47:36 AM 6/29/2009
I want to be able to add the call log record for the check date that corresponds to the checks being processed and show under the check date column. This report is grouped by name.
Any help would be great! Thanks.

 

[Turkbear]

Hi,
What are you linking the tables by ?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[mdj3366]

The report contains 4 tables: calllog, employee, payment and group. I am sure that the linking is weird, but even when i ran a test report using just the calllog the data being returned is this:
name check_date
person 03/27/2009
person 04/10/2009
person 04/10/2009
person 04/24/2009
person 04/24/2009
person 05/08/2009
person 05/08/2009
person 05/22/2009
person 05/22/2009
person 05/22/2009
person 06/05/2009
person 06/19/2009
person 06/19/2009
person 07/03/2009
person 07/03/2009

This is incorrect as there is only one check date, even if the payment contains more than one week of pay. Our database creates a record for each call, with each call returning a check date. Each person must call once per week. If someone is paid on a biweekly schedule, then there will be 2 calls with the same check date. If i can have the test report return the correct info., i am sure i will be able to apply to the other report, even if i have to use a subreport. I hope this makes sense!

 

[Madawc]

Try grouping and suppressing the detail lines. You might get the result you want using Maximum for a summary total. Or a running total with a formula, try and see.

If you're not already familiar with Crystal's automated totals, see FAQ767-6524.


Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10