need only last transaction to show in report

[Kibee]

Hi there

I am using Crystal 8.5 off a clipper database

I am trying to build a report that shows the last time a donantion was received from donors... then group that information by year

So far it looks like this (grouped by donor number)

{Donor Number}

{payment number} {amount} {date}

eg

123465

6543 $45.00 16/04/2003
2547 $100.00 22/03/2003


i can do a maximum ({date},{donor number}) in order to find the last donation date for each doner. the problem is then when I add a higher level of group - ie by Year it will then give the maximum donation date for each doner in that year.

The aim of my report is to show exactly when each donor last donated... NOT their last donaiton for each year.

Any ideas???
thanks

K

 

[lbass]

I think you could handle this by doing a subselect in the where clause of your SQL query. You would go to database->show SQL query and in the "where" section add a clause like:

WHERE
Table.`Payment Date` = (SELECT MAX(AKA."Payment Date") FROM Table AKA where AKA."Donor Number" = Table."Donor Number")

Substitute the correct table name for "Table" and field names for "Payment Date" and "Donor Number", leaving "AKA" as an alias table name. If there are other "Where" clauses, add this clause at the end, prefixing it with "and".

This should return only the most recent date per donor, so that when you add the year group, you will still only get one record per donor, appearing in the appropriate year.

Be sure to save your report under a different name before trying this.

-LB

 

[Kibee]

Hi there LB

thanks heaps for that. i have tried changing the SQL and whilst it seems to be working i have waited nearly and hour and have managed so far to only bring back 247 records out of the 13 000 or so i am needing. I have realised too they may have had several transactions on their last day and changed it to be looking for the max payment number rather than date.

any other suggestions as to how i could do this? does the time it has taken to run this so far seem wrong? is there anyway I can speed it up?

thanks

K

 

[lbass]

I'm not sure why this would take so long. I would try running the report with a small sample of records first, maybe selecting specific donor numbers, to test how well this is working. The report should only return one record per donor number--are you saying you have 13,000 donors?

-LB

 

[Madawc]

If you can't get lbass's suggestion to work, you could try creating a subreport that shows the last donation, and place it in the footer for each year. Subreports are slow, but they get there in the end.

Madawc Williams
East Anglia, Great Britain

 

[dgillz]

Group your report by donor, and sort it so that the last donation is at the end of the group. Then drag the fields in question to the group footer, and hide the details section.



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

http://www.trianglepartners.com