Solved

SQL Query Help, With Date Condition

Posted on 2008-10-21
11
375 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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, when working with VBA, learn some techniques for writing readable and easily maintained code.

707 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

15 Experts available now in Live!

Get 1:1 Help Now