Solved

SQL Query Help, With Date Condition

Posted on 2008-10-21
11
382 Views
Last Modified: 2012-05-05
I am trying to get a list of all Institution codes where the patient is still active. This occurs when the [On Study Date] is greater than 1/1/2006, so I am trying to put a Y if they have more than one patient with an On Study Date greater than 1/1/2006 and a N if they have 0 patients meeting that requirement. However my query will not work...

SELECT qryInstitution_Ancillary.[Institution Code], (IIF COUNT(tblPatientDS.[On Study Date]>='1/1/2006') > 0,'Y','N') AS Expr1
FROM qryInstitution_Ancillary LEFT JOIN tblPatientDS ON qryInstitution_Ancillary.[Institution Code] = tblPatientDS.[Principal Institution];

My error is.. Syntax error (comma) in query expression '(IIF COUNT(tblPatientDS.[On Study Date]>='1/1/2006') > 0,'Y','N')'.

Any thoughts would be greatly appreciated. Thanks.
0
Comment
Question by:kpurchase
  • 5
  • 3
  • 3
11 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22767126


SELECT qryInstitution_Ancillary.[Institution Code], IIF((select  COUNT(*) from  tblPatientDS where [On Study Date]>=#1/1/2006#) > 0,'Y','N') AS Expr1
FROM qryInstitution_Ancillary LEFT JOIN tblPatientDS ON qryInstitution_Ancillary.[Institution Code] = tblPatientDS.[Principal Institution];


0
 

Author Comment

by:kpurchase
ID: 22767230
That looks good.. But it now lists a Y for every patient with that institution code. Anyway that we could get it so that it is just a single Y or N for each institution code?
0
 

Author Comment

by:kpurchase
ID: 22767245
Actually I think thats just.. adding select distinct. But I get all Y's when doing so, which is incorrect.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22767265
so you want individual listing

SELECT qryInstitution_Ancillary.[Institution Code], IIF([On Study Date]>=#1/1/2006#,'Y','N') AS Expr1
FROM qryInstitution_Ancillary LEFT JOIN tblPatientDS ON qryInstitution_Ancillary.[Institution Code] = tblPatientDS.[Principal Institution];


0
 
LVL 18

Expert Comment

by:mdougan
ID: 22767330
You are missing a right parenthesis on your COUNT statement.  However, I'm not so sure that count is going to work unless you add a Group By clause.  I think I'd prefer to do this with a UNION

SELECT DISTINCT qryInstitution_Ancillary.[Institution Code], 'Y'
FROM qryInstitution_Ancillary
LEFT JOIN tblPatientDS ON qryInstitution_Ancillary.[Institution Code] = tblPatientDS.[Principal Institution]
WHERE tblPatientDS.[On Study Date]>='1/1/2006'

UNION

SELECT DISTINCT qryInstitution_Ancillary.[Institution Code], 'N'
FROM qryInstitution_Ancillary
LEFT JOIN tblPatientDS ON qryInstitution_Ancillary.[Institution Code] = tblPatientDS.[Principal Institution]
WHERE NOT EXISTS (SELECT tblPatientDS.[Principal Institution]
                                   FROM tblPatientDS
                                   WHERE tblPatientDS.[Principal Institution] = qryInstitution_Ancillary.[Institution Code]
                                   AND  tblPatientDS.[On Study Date]>='1/1/2006')
0
 

Author Comment

by:kpurchase
ID: 22767371
We are on the right track, and real close.. But I think we are missing each other slightly. What I need is a listing of either a Y for an Institution Code or a N. The last solution you listed gave me a Y or N for each patient in tblPatientDS and when I put SELECT DISTINCT in front of it, it gave me a Y or N for every Institution Code that had both or just a Y or just a N for Institution Codes that just had one or the other. Does that make more sense?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22767439
kpurchase,
which post are you referring to?
0
 
LVL 18

Expert Comment

by:mdougan
ID: 22767456
Another way to do this would be this way:

SELECT  qryInstitution_Ancillary.[Institution Code],
MIN(IIF(tblPatientDS.[On Study Date]>='1/1/2006', 'Y', 'N')) AS flag
FROM qryInstitution_Ancillary
LEFT JOIN tblPatientDS ON qryInstitution_Ancillary.[Institution Code] = tblPatientDS.[Principal Institution]
GROUP BY qryInstitution_Ancillary.[Institution Code]

This will return the Y value if it exists for an institution code, otherwise will return an N
0
 

Author Comment

by:kpurchase
ID: 22767468
capricorn, my previous post was in response to yours as I had not seen mdougan's response until just now so I will try that out.
0
 
LVL 18

Accepted Solution

by:
mdougan earned 500 total points
ID: 22767472
Actually, maybe that MIN has to be MAX
0
 

Author Closing Comment

by:kpurchase
ID: 31508264
worked beautifully once adding the MAX to it...
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question