Solved

SQL Query Help, With Date Condition

Posted on 2008-10-21
11
376 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 119

Expert Comment

by:Rey Obrero
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
 
LVL 119

Expert Comment

by:Rey Obrero
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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 119

Expert Comment

by:Rey Obrero
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

930 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now