Jack and Pat
drawbridgej at sympatico.ca
Tue Dec 7 10:33:18 CST 2010
Pedro, I don't have any specific ideas, but I did remove the key I had on my test data. I added a field called ID (autonumber) and made it PK but only for testing. I don't use The Id field in the query. I wasn't sure if you were using Min or Max. I adjusted my table to include your data. When I used it for Max pDate and just selected the additional field, I got 2 responses - 1 for each value of additional field. TO remove that problem I adjusted the query to use Max (PDate) and Max(AdditionalField) Here's my data and query: PedroTbl (data) patient_number PDate AdditionalFld id 1 1/1/2010 5 1 2 2/2/2010 3 2 2 3/3/2010 3 3 2 3/3/2010 4 4 2 2/3/2010 4 5 Query: SELECT pt.patient_number, pt.PDate,pt.AdditionalFld FROM (SELECT Patient_Number , Max(pDate) AS min_PDt , max(AdditionalFld) as min_AF FROM PedroTbl GROUP BY Patient_Number ) AS m INNER JOIN PedroTbl AS pt ON (m.Patient_Number = pt.patient_number) AND (m.min_PDt = pt.PDate) and (m.min_AF = pt.AdditionalFld) Query result: patient_number PDate AdditionalFld 1 1/1/2010 5 2 3/3/2010 4 Hope this is useful to you. You can just replace Max with Min to get the Minimums if that's what you're doing. Good luck 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 5:18 PM To: AccessD at databaseadvisors.com Subject: Re: [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 > > -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com