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
Solved

Query Syntax

Posted on 2013-05-21
6
174 Views
Last Modified: 2013-06-08
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.TypeCodeID)=955)) OR (((dbo_HistoEvent.StudyID)=8621) AND ((dbo_HistoEvent.TypeCodeID)=985)) OR (((dbo_HistoEvent.StudyID)=8621) AND ((dbo_HistoEvent.TypeCodeID)=986));
0
Comment
Question by:sharpapproach
6 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39185206
Post a sample of the raw data
Post a sample of the *exact* results you want
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 39185222
SELECT dbo_HistoEvent.StudyID
FROM dbo_HistoEvent
WHERE dbo_HistoEvent.StudyID=8621
AND dbo_HistoEvent.TypeCodeID= IN (955, 985, 986)
GROUP BY dbo_HistoEvent.StudyID
HAVING ABS(SUM(dbo_HistoEvent.CodeID * dbo_HistoEvent.Set1Flag)) = 955
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39186495
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? */

Open in new window

0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39187159
Paul,

you missed the latter part of the OPs statement:  "... , and 985 and 986 are not"
0
 
LVL 84
ID: 39187162
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.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39187473
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?
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

829 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