[AccessD] group min - max

How do you decide which record to use here:
> 002    3-2-10    8
> 002    3-2-10    6

If there only one entry per day per patient, you can use Order By and First/Last.

Select pID, First(pDate),First(POtherValue)
from tblPatientData
Group By pID
Order by pID,pDate.

It would be a good idea to index the table on pID,pDate :-)

If there are multiple entries per day, you need a way to identify which  record you want.  For 
the data and results given, this would work

...
Order by pID,pDate,pOtherValue

If you want the maximum value on the first date, use

...
Order by pID,pDate,pOtherValue DESC


-- 
Stuart

On 6 Dec 2010 at 13:36, pedro at plex.nl wrote:

> Dear list,
> 
> normally i use Group by and min max, to create a list of min or max
> (or first-last) dates per Patient. 
> 
> But when there is a third column which has different values, I'll make
> a key from date and patient-number and then ad the extra value. How
> can i do this in one simple query?
> 
> I have
> 
> 001    1-1-10    5
> 001    2-2-10    7
> 002    3-2-10    8
> 002    3-2-10    6
> 
> i need 
> 
> 001    1-1-10    5
> 002    3-2-10    6
> 
> Thanks
> 
> Pedro
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>