Solved

SQL Query Help, With Date Condition

Posted on 2008-10-21
11
385 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

737 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