William Benson (VBACreations.Com)
vbacreations at gmail.com
Thu Dec 29 10:14:43 CST 2011
Not "one record" per "date criteria" I meant one CODE per CODE criteria. Anyway, the wording was garbled but the point was the same, you wouldn't get any codes that you did not specify, and in the first criteria I was only asking for codes = 326 and 327, even though I was using an intermediate query where code = 325 to limit the records to search in. Thus I needed to add another query (pulling the code=325 records) which had the relationship you established... in order to build the complete story. I feel certain there must be a more efficient way to ask this in one query and not union two partials together but I am not smart / practiced enough. -----Original Message----- From: William Benson (VBACreations.Com) [mailto:vbacreations at gmail.com] Sent: Thursday, December 29, 2011 11:10 AM To: Access Developers discussion and problem solving Subject: RE: [AccessD] query select in column Well, I said 325 not 25. I could say that I thought you would figure out as an intellectual exercise that of course you will only get one record per date criteria (ie, 326 and 327)... But that would be arrogant, pedantic (and false!), I was in fact, just half-witted. Here gets them all. Select tbl2.pat, tbl2.opendate, tbl2.code from tbldialysis as tbl2 inner join (select distinct pat from tbldialysis where code=325) as tbl1 on tbl1.pat = tbl2.pat where tbl2.code in (326,327); union Select tbl2.pat, tbl2.opendate, tbl2.code from tbldialysis as tbl2 inner join (select distinct pat from tbldialysis where code in (326,327) ) as tbl1 on tbl1.pat = tbl2.pat where tbl2.code =325 order by Pat, OpenDate -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of pedro at plex.nl Sent: Thursday, December 29, 2011 4:13 PM To: accessd at databaseadvisors.com Subject: Re: [AccessD] query select in column Hello Gustav, that's correct (to much on my mind at the end of the year) But no i only get as result the records with code 326 and 327). William mentioned: "Join the table to a select query whose only criterion is code =25" -- i tried this, but i still only have the records with code 326 and 327. What is wrong? (more and more on my mind!!) Pedro In antwoord op: > From: "Gustav Brock" <Gustav at cactus.dk> > To: <accessd at databaseadvisors.com> > Date: Thu, 29 Dec 2011 15:17:53 +0100 > Subject: Re: [AccessD] query select in column > > > Hi Pedro > > Shouldn't last line read: > > tbl1.pat = tbl2.pat where tbl2.code in (326,327); > > /gustav > > > >>> pedro at plex.nl 29-12-2011 14:54 >>> > Hello William, > > I made a second table with only the code=325 records in it. > But i get an syntax-error when using the query; > > Select tbl2.pat, tbl2.opendate, tbl2.code from tbldialysis as tbl2 inner > join (select distinct pat from tbldialysis where code=325) as tbl1 on > tbl1.pat = tbl2.pat and tbl2.code in (326,327); > > i can't figure out why (after correction of misspelled words? > > What am i doing wrong? > > Pedro > > > > > In antwoord op: > > > From: William Benson <vbacreations at gmail.com> > > To: Access Developers discussion and problem solving > > Date: Thu, 29 Dec 2011 07:53:46 -0500 > > Subject: Re: [AccessD] query select in column > > > > > > Join the table to a select query whose only criterion is code =25. > > > > Select tb2.pat , tbl2.opendate , tbl2.code from tbldialysis as tb2 inner > > join (select distict pat from tbldialysis where code= 325) as tbl1 on > > tbl1.pat = tbl2.pat and tbl2.code in (326,327) > > On Dec 29, 2011 6:59 AM, <pedro at plex.nl> wrote: > > > > > Dear list. > > > > > > i have a table with patients. > > > > > > tblDialysis > > > > > > Pat OpenDate Code > > > 1 01-01-2011 325 > > > 2 02-02-2011 325 > > > 2 03-03-2011 326 > > > 2 04-04-2011 327 > > > 3 05-05-2011 325 > > > 3 06-06-2011 326 > > > 4 07-07-2011 325 > > > 4 08-08-2011 327 > > > 5 09-09-2011 326 > > > > > > i need all records of patients who have a code 325 and 326 0r 325 and 327 > > > > > > > > > result > > > > > > Pat OpenDate Code > > > 2 02-02-2011 325 > > > 2 03-03-2011 326 > > > 2 04-04-2011 327 > > > 3 05-05-2011 325 > > > 3 06-06-2011 326 > > > 4 07-07-2011 325 > > > 4 08-08-2011 327 > > > > > > any idea's > > > > > > 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