Grouping/Running Totals Problem

[deemat]

I am using CR XI
Trying to use only the record with the maximum date in running totals and grouping when more than on date exists for that entry.

For Example:
GP1 (ID)
ID Date Transferred
PPQ 01/02/2011
PPQ 03/01/2011
PPQ 05/03/2011

I need to further group by month which changes my totals.
GP 1 Jan 2011
GP 2 (ID)
ID Date Transferred
PPQ 01/02/2011

GP 1 March 2011
GP 2 (ID)
PPQ 03/01/2011

GP 1 May 2011
GP 2 (ID)
ID Date Transferred
PPQ 05/03/2011


I only want the entry for May 2011 to show in the report and in totals.

GP 1 Jan 2011
GP 2 (ID)
ID Date Transferred


GP 1 March 2011
GP 2 (ID)
ID Date Transferred


GP 1 May 2011
GP 2 (ID)
ID Date Transferred
PPQ 05/03/2011


Any help would be greatly appreciated.

 

[lbass]

Create a SQL expression {%maxdt}:

(
select max(`date transferred`)
from table A
where A.`ID` = table.`ID`
)

Then go to record->selection formula->record and enter:

{table.date transferred} = {%maxdt}

This will return on the most recent date per ID to the report, so you can group as you wish.

-LB

 

[deemat]

Thanks for your help but I don't think I am following your instructions correctly. I keep getting an error. This is what I have below.

(
select max('date transferred')
from table A
where A.'ID' = mum_daily_subm.'ID'
)

The name of my table is mum_daily_subm

 

[lbass]

What type of database are you using? Note that the punctuation I showed would be appropriate for an Access database. You are not mimicking my punctuation correctly--It is not a single quote, but instead, the punctuation mark to the left of the "1" on the top of your keyboard. But this may be irrelevant. You should use the punctuation that you see used in database->show SQL query.

-LB