Extracting 3rd date

[rier]

Can anyone help?

I have an accounts table which contains both credit and debits. The history for one customer might look something like:

CustomerID Type Amount Date
1000000020 Debit 20.00 01/05/2002
1000000020 Credit 20.00 03/05/2002
1000000020 Debit 50.00 03/05/2002
1000000020 Credit 50.00 08/05/2002
1000000020 Debit 20.00 11/05/2002
1000000020 Credit 20.00 14/05/2002
1000000020 Debit 10.00 15/05/2002
1000000020 Credit 10.00 18/05/2002

The report I am producing will show all customers, but the 'PaidThirdBill' formula field will contain the date when the third payment(credit) was made - assuming they have made three)

In the above example, I would expect the result to be '14/05/2002' as this is the 3rd credit date.

Hope that is clear and can anyone 'guide' me?

Thanks in advance

Richard

 

[BlurredVision]

A running count might work here.. When the count is = to 3 then display the date.

Hope this helps Brian

http://www.crystalinfo.net

 

[kenhamady]

The trick is where do you want this date to appear. At the bottom of the group is easy. At the top of the group is harder. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Ngolem]

that and a few more rules as to when this credit date is to be shown.

Is your data always paired like this....it looks too tidy for real data...in my experience with working with this situation

So ALL you want is the 3rd credit?? Even if the first two don't completely pay the bills of the other debits before them.

Show us what the report is to look like...counting until you reach a third credit is easy enough...Also what do you want to happen if there is less than 3 credits? Jim Broadbent

 

[rier]

Ngolem

You're right to suspect that the data given in my example is always this neat, actual records in the accounts table would be more:

CustomerID Credit Debit Date InvNo
1000000020 NULL 20.00 01/05/2002 50123
1000000020 10.00 NULL 03/05/2002 50123
1000000976 NULL 43.00 04/05/2002 50144
1000000020 10.00 NULL 06/05/2002 50123
1000000020 NULL 20.00 08/05/2002 50161
1000000020 20.00 NULL 14/05/2002 50161
1000000020 NULL 20.00 15/05/2002 50172
1000000976 43.00 NULL 04/05/2002 50144
1000000020 20.00 NULL 16/05/2002 50172

Because they are in date order, the customer records can be entwined.

I am attempting to generate a report and would like to show the following for customer 1000000020.

CustomerID 1stCreditDate 2ndCreditDate 3rdCreditDate
1000000020 03/05/2002 06/05/2002 14/05/2002

The following shows the formula I am using to try and get the FirstCreditDate

--------------------------------------------
WhilePrintingrecords;
numbervar FirstCreditDateCount;
If not isnull({Accounts.Credit}) then
FirstCreditDateCount:=FirstCreditDateCount+1;
If FirstCreditDateCount = 1 Then
{Accounts.Date}

---------------------------------------------

So what am I doing wrong?

Hope you can help

Thanks

Richard