LAST DATE IN A SEQUENCE OF DATES!! 6

[DaveDup]

Please help!!

I have a person with multiple dates listed for them in a child table. I only want the record with the highest date to appear in the report. How can I only select the most recent date from multiple records??

 

[synapsevampire]

You can either use a Top N report, or you can group by the person field and by the date field and place the fields in the date group footer, thus showing only the last date field.

-k

 

[chelseatech]

You can also use group selection formula

The group selection formula is
{person.datefield}=
Maximum({person.datefield},{person.groupID})

Where the {Person.GroupID} is the field you are grouping your data by to get each person seperately in the report. If you haven't got a group by this field, then create a group to do this.

Also watch your summaries. They are calculated prior to the Group Selection. If you need totals in your report, using Running Total Fields which are calculated after the Group Selection Formula.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[lyanch]

My personal preference is to add a correlated subquery in the SQL itself. It has the advantage of keeping and rows you don't want from being returned. This is especially true where you can have large number of rows returned that don't meet the criteria. I usually do this last after I have all the fields and other parameters done.

To add the subquery:

Go to Database-> show SQL

In the WHERE section add last:

AND table.date = (select max(t.date) from table t
where t.id = table.id)

where table.id is the id from the main SQL that you want the date maximized for and t.id is the same id field in the subquery)

For instance..
table
CustomerContacts
fields

contactId
contactDate
contactNote
contactBy

would look like:

AND CustomerContacts.date = (select max(cc.date) from CustomerContacts cc
where cc.contactId= CustomerContacts.contactId)


This would limit customer contact info in the main query to only return information about the latest contact..

Lisa

 

[EMP1]

Lisa,

I am new to Crystal Reports. I am using CR 9.2.

I use the report wizard to build my report using an ODBC DSN on an Access database. When I go to the menu Database -> Show SQL Query... I can view the SQL Query but I cannot edit it. Is there any thing I need to do to make it editable.

Thanks

Jon

 

[DaveDup]

It worked!!! Thank to everyone for the input!!!

 

[synapsevampire]

Lisa: The Top N/Group record selection in conjunction with perform grouping on server does basically the same thing.

-k

 

[lyanch]

-k
I usually have problems getting the "grouping on server" to work... which may be me... but it seems by the time i add all my parameters and criteria and fields.. it never seems to want to group on the server.

Jon..

I am not using 9 yet, but i believe it is even easier with the SQL command option...

Lisa

 

[EMP1]

Lisa,

Thanks.

Jon