[AccessD] group min - max

Pedro,

In your test data, you couldn't have a key  of Patient-number + Date  with  
Patient 002 and 3-2-10.(duplicate??)
I changed the data a little and this is based on 2 queries.

PedroTbl:
patient_number	PDate	AdditionalFld
1	1/1/2010	5
1	2/2/2010	7
2	3/2/2010	8
2	3/3/2010	6
2	3/4/2010	9


I created a query#1  PedroGrp as

SELECT PedroTbl.patient_number, Min(PedroTbl.AdditionalFld) AS
MinOfAdditionalFld
FROM PedroTbl
GROUP BY PedroTbl.patient_number;

And this query #2 based on the table PedroTbl and the first query PedroGrp

SELECT 
PedroTbl.patient_number
, PedroTbl.PDate
, PedroTbl.AdditionalFld
FROM PedroTbl 
INNER JOIN PedroGrp ON
 (PedroTbl.AdditionalFld = PedroGrp.MinOfAdditionalFld) 
AND 
(PedroTbl.patient_number = PedroGrp.patient_number);

Hope this is useful.
jack

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of pedro at plex.nl
Sent: Monday, December 06, 2010 1:36 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] group min - max

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