[AccessD] group min - max

Hello Jack,

i used your query, but it gives the max-min value of the additional-field (last Column), and with many the same dates and additional-field values, i have more then one record.
It is more important to me to have the max or min date, with the additional-field value.

Let me explain more
for example.

i have  

001   2-2-2010  2
001   3-3-2010  3
001   3-3-2010  4
001   2-3-2010  4

Your query (max) result gives:

001   3-3-2010  4
001   2-3-2010  4

I'll  need 

001   3-3-2010  4
(in one query)

the example can give you a wrong image of the data, because the dataset for the years needed is 750000 records and patients can have up to a few hundred records each.

Pedro










>From: Jack and Pat <drawbridgej at sympatico.ca>
>To: "'Access Developers discussion and problem solving'"
>         <accessd at databaseadvisors.com>
>Date: Mon, 6 Dec 2010 10:21:58 -0500


>Pedro,
>
>I redid the query in original response  as a single query
>
>SELECT pt.patient_number
>      , pt.PDate
>      ,pt.AdditionalFld
>   FROM ( SELECT Patient_Number
>               , Min(AdditionalFld) AS min_AF
>            FROM PedroTbl
>          GROUP
>              BY Patient_Number ) AS m
>INNER
>   JOIN PedroTbl AS pt
>     ON (
>       pt.Patient_number = m.Patient_Number
>    AND pt.AdditionalFld = m.min_AF
>        )
>
>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