Last date of service detail

[cagoh]

I have a database that contains the following fields

client # Date start time State Item
1 10/1/03 1:02 PM oh ap
2 10/15/03 5:04 PM ky ba
1 9/1/03 5:00 AM oh cd
2 10/4/03 8:00 AM oh ap
1 10/18/03 9:00 PM oh fp

I want the last date and time where item = ap, or ba, or cd and bring the following detail back

client # Date State Item
1 10/1/03 oh ap
2 10/15/03 ky ba

Sorry this is probably so simple, i just can't figure it out.

thanks for your help!!!

 

[MJRBIM]

This isn't actually a formula just a grouping and sorting method that would return data like this...

CLIENT# DATE START_TIME STATE ITEM
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
1 10/01/2003 1:02 PM oh ap
2 10/15/2003 5:04 PM ky ba
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

1.) SELECT EXPERT : Table.Item "is one of" cd
ba
ap

2.) INSERT GROUP : Table.CLIENT#

3.) RECORD SORT ORDER : Table.DATE (Ascending)

4.) RECORD SORT ORDER : Table.START_TIME (Ascending)

5.) COPY all the fields from your DETAIL into the GROUP FOOTER

6.) SUPPRESS the GROUP HEADER and DETAIL sections of your report.

Hope it helps...

 

[bdreed35]

Try this:

Group on Item.
Create a Maximum Summary field based upon the Date field.
Move the Summary to the Item Group Header.
Add your client#, and state fields to the group header as well.
Suppress the details.
Add this to your record selction criteria to eliminate values other than ap, ba, or cd.

{table.item} IN ["ap","ba","cd"]

~Brian