[AccessD] group min - max

Hello Jack,

i have no uniek key in this data, but i can change the table so that i have one.
Then i could make a normal max or min query on the date with a group by  on Patientnumber and use an update query to ad the additional field with value.

Or do you have a better idea?

Pedro
 


In antwoord op:

> From: Jack and Pat <drawbridgej at sympatico.ca>
> To: "'Access Developers discussion and problem solving'"
> Date: Tue, 7 Dec 2010 10:35:37 -0500
> Subject: Re: [AccessD] group min - max
> 
> 
> Pedro,
> You indicated you were making a key out of patient-number and Date. That
> means you can't have more than one record with the same patient-number and
> Date. I'm confused-- what is the key to the record? As Stuart mentioned, How
> to you identify specific records?
> I made up some data yesterday, but wasn't sure of your structures and real
> requirements.
> 
> Sorry about that.
> 
> jack
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of pedro at plex.nl
> Sent: Tuesday, December 07, 2010 2:38 PM
> To: pedro at plex.nl
> Cc: AccessD at databaseadvisors.com
> Subject: Re: [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
> 
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
>