Return value from aggregate using Parent Child relationship

microsoft.public.dotnet.framework.adonet

9 Dec 2004 9:09 PM

Mike Henry

Hello,
I'm trying to create a filter on a datatable in a dataset. I have 2
tables 1) Parent - 'Patients' and 2) Child - 'Flow_Cyto' with it being a one
to many relationship. The Flow_Cyto table contains a PatientId, Date and
Value. What I'm trying to do is filter the 'Patients' table in a datagrid by
filtering on the most recent value at the most recent date.

Ie Display all patients whose most recent 'Flow_Cyto value is less than 10?

So far I can create a new column in the 'Patients' table that returns either
the Max(Flow_Cyto.Date) code:

dsSAC.Tables("PATIENTS").Columns.Add("MOST_RECENT_CD4_DATE", GetType(Date),
"Max(Child(PATIENTS_FLOW).SAMPLE_DATE)")

or the Max(Flow_Cyto.Value) code:

dsSAC.Tables("PATIENTS").Columns.Add("MOST_RECENT_CD4", GetType(Double),
"Max(Child(PATIENTS_FLOW).CD4_ABSOLUTE)")

but not the Flow_Cyto.Value at the Max(Flow_Cyto.Date).

Any help would be much appreicated.

Mike Henry