?
Solved

Query Syntax

Posted on 2013-05-21
6
Medium Priority
?
177 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
[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
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 48

Accepted Solution

by:
Dale Fye earned 1500 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 49

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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 48

Expert Comment

by:Dale Fye
ID: 39187159
Paul,

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

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

770 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