[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