Solved

Query Syntax

Posted on 2013-05-21
6
170 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Paul,

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server views 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 Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

772 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

11 Experts available now in Live!

Get 1:1 Help Now