Finding the earliest date for a group of records

[griffter]

Hi All

Hope you can help again. I need to create the following report

Site Investigator SiteName ActDate Status 1stPat LstPat
155 Mr Bloggs Blogg Site 23/12/04 Active ? ?

In the 1stPat column I need to display the 1st patient activated there is a field patients.screeningdate with all the patient screening dates relevant to the site

I have tried to group by site id and use the minimum(patients.screeningdate) but it is displaying the first date for all the sites and not for each site id.

Can you help?

 

[griffter]

Not to worry I've found the solution using summary field

 

[griffter]

Just in case anyone else gets stuck on this one this was the solution. Using the minimum\maximum formula with a condition on the group, ie

Minimum({YourTable.YourField}, {YourTable.YourField})
Minimum({fld,fldcond ie the field records grouped by)

Gives the earliest date for each group of records

Maximum({YourTable.YourField}, {YourTable.YourField})
Maximum(fld,fldcond ie the field records grouped by)

Gives the latest date for each group of records