Jack and Pat
drawbridgej at sympatico.ca
Tue Dec 7 09:35:37 CST 2010
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