sharpapproach
asked on
Query Syntax
I am having trouble with a Query. Everything I try doesn't work. In short, it looks for a StudyID and if 955 = true but 985 or 986 are false, then I don't want it to show as a result. I am trying to get a list of studies that 955 is checked on a form, and 985 and 986 are not.
StudyID TypeCodeID Set1Flag
8621 955 -1
8621 985 -1
8621 986 0
Here is the SQL Statement. How can I get the result I desire?
SELECT dbo_HistoEvent.StudyID, dbo_HistoEvent.TypeCodeID, dbo_HistoEvent.Set1Flag
FROM dbo_HistoEvent
WHERE (((dbo_HistoEvent.StudyID) =8621) AND ((dbo_HistoEvent.TypeCodeI D)=955)) OR (((dbo_HistoEvent.StudyID) =8621) AND ((dbo_HistoEvent.TypeCodeI D)=985)) OR (((dbo_HistoEvent.StudyID) =8621) AND ((dbo_HistoEvent.TypeCodeI D)=986));
StudyID TypeCodeID Set1Flag
8621 955 -1
8621 985 -1
8621 986 0
Here is the SQL Statement. How can I get the result I desire?
SELECT dbo_HistoEvent.StudyID, dbo_HistoEvent.TypeCodeID,
FROM dbo_HistoEvent
WHERE (((dbo_HistoEvent.StudyID)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I am trying to get a list of studies ... (where) 955 is checked on a form
SELECT DISTINCT dbo_HistoEvent.StudyID
FROM dbo_HistoEvent
WHERE dbo_HistoEvent.TypeCodeID = 955
AND Set1Flag = -1 /* true is minus one? */
Paul,
you missed the latter part of the OPs statement: "... , and 985 and 986 are not"
you missed the latter part of the OPs statement: "... , and 985 and 986 are not"
Is this querying a SQL Server table? If so, try changing "=-1" to "<>0" or "=1". SQL Server doesn't have a boolean field, so most use a Bit field for that. Bit fields contain values of 0 or 1 only, with 0=False and 1=True.
I think fyed's approach is likely to be the right one here, although the having clause is 'interesting' :)
I've also just noticed this is Access, so I'm not that sure about the minus one here. My memory of Access is that 1 = true, 0 = false
as fyed points out my over simplistic approach ignores 985 and 986, so there has to be some form of aggregated view by studyid, then exclude those where 985 (or?) 986 play a part.
I should have noticed all those brackets, what is it with Access and the plethora of parentheses?
I've also just noticed this is Access, so I'm not that sure about the minus one here. My memory of Access is that 1 = true, 0 = false
as fyed points out my over simplistic approach ignores 985 and 986, so there has to be some form of aggregated view by studyid, then exclude those where 985 (or?) 986 play a part.
I should have noticed all those brackets, what is it with Access and the plethora of parentheses?
Post a sample of the *exact* results you want