Query returning negative number - what's wrong with this SQL?!

E

Emelina Bumsquash

I have a query as below and can't figure out what's wrong with it. It returns
only a '-1' if the conditions are true irrespective or how many cases it's
true in i.e. i made 2 records have the conditions of this query so it should
have returned '2' but it just returned '-1' ... can anyone help?

NB the Lost_FollowUp field is a yes/no checkbox - could that have something
to do with it?

SELECT Screened.Site,

(Sum(IIf([Consent]=1 And [ArmAllocated]=1 And [Violations_Dropouts]=2,1,0))
Or Sum(IIf([Consent]=1 And [ArmAllocated]=1 And [Violations_Dropouts]=1 And
[Lost_FollowUp]=-1,1,0))) AS [AC Chosen Withdrawal]

FROM (Recruitment INNER JOIN Randomisation ON Recruitment.StudyNumber =
Randomisation.StudyNumber) INNER JOIN Screened ON Recruitment.StudyNumber =
Screened.StudyNumber
GROUP BY Screened.Site;
 
S

Stefan Hoffmann

hi Emelina,

Emelina said:
I have a query as below and can't figure out what's wrong with it. It returns
only a '-1' if the conditions are true irrespective or how many cases it's
true in i.e. i made 2 records have the conditions of this query so it should
have returned '2' but it just returned '-1' ... can anyone help?
NB the Lost_FollowUp field is a yes/no checkbox - could that have something
to do with it?
Click to expand...
No. The problem is the "or":
(Sum(IIf([Consent]=1 And [ArmAllocated]=1 And [Violations_Dropouts]=2,1,0))
Or Sum(IIf([Consent]=1 And [ArmAllocated]=1 And [Violations_Dropouts]=1 And
[Lost_FollowUp]=-1,1,0))) AS [AC Chosen Withdrawal]
Click to expand...
Stripping the IIf's you have:

(Sum() Or Sum()) AS [AC Chosen Withdrawal]

This returns a boolean, which is as value either 0 (False) or -1 (True).


mfG
--> stefan <--
 
S

Sergio

Hi,

Could someone help me, please. I am new in Access programming.
I have the following query

SELECT MP06.TER, MP06.MEST
FROM MP06
WHERE (((MP06.TER) Like "11*") AND ((MP06.MEST) Not Like "11*") or
((MP06.TER) Like "15*") AND ((MP06.MEST) Not Like "15*") or ((MP06.TER)
Like "19*") AND ((MP06.MEST) Not Like "19*") );

But in fact those numbers 11, 15, 19 etc I need to take from a table AREA.
There are much more of those numbers.
Is there a way to do it?
Thanks in advance.

Sergei
 
E

Emelina Bumsquash

Ah, thanks Stefan - that's helpful. is there any way to get around this do
you know? i really want to be able to count the number of instances in which
either of the 2 scenarios occur!

Stefan Hoffmann said:
hi Emelina,

Emelina said:
I have a query as below and can't figure out what's wrong with it. It returns
only a '-1' if the conditions are true irrespective or how many cases it's
true in i.e. i made 2 records have the conditions of this query so it should
have returned '2' but it just returned '-1' ... can anyone help?
NB the Lost_FollowUp field is a yes/no checkbox - could that have something
to do with it?
Click to expand...
No. The problem is the "or":
(Sum(IIf([Consent]=1 And [ArmAllocated]=1 And [Violations_Dropouts]=2,1,0))
Or Sum(IIf([Consent]=1 And [ArmAllocated]=1 And [Violations_Dropouts]=1 And
[Lost_FollowUp]=-1,1,0))) AS [AC Chosen Withdrawal]
Click to expand...
Stripping the IIf's you have:

(Sum() Or Sum()) AS [AC Chosen Withdrawal]

This returns a boolean, which is as value either 0 (False) or -1 (True).


mfG
--> stefan <--
Click to expand...
 
J

John Spencer

Perhaps what you want is to count the number of instances where one set
of conditions or the other is true.

Sum(IIf([Consent]=1 And [ArmAllocated]=1 And [Violations_Dropouts]=2)
Or ([Consent]=1 And [ArmAllocated]=1 And [Violations_Dropouts]=1 And
[Lost_FollowUp]=-1),1,0) AS [AC Chosen Withdrawal]

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
E

Emelina Bumsquash

Thanks for this John, this is exactly the calculation i want to do. however,
the SQL you've kindly done below isn't working in my query - it's saying
there's the 'wrong number of arguments used with function in query
expression.... any ideas? Thanks for your time

John Spencer said:
Perhaps what you want is to count the number of instances where one set
of conditions or the other is true.

Sum(IIf([Consent]=1 And [ArmAllocated]=1 And [Violations_Dropouts]=2)
Or ([Consent]=1 And [ArmAllocated]=1 And [Violations_Dropouts]=1 And
[Lost_FollowUp]=-1),1,0) AS [AC Chosen Withdrawal]

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Emelina said:
I have a query as below and can't figure out what's wrong with it. It returns
only a '-1' if the conditions are true irrespective or how many cases it's
true in i.e. i made 2 records have the conditions of this query so it should
have returned '2' but it just returned '-1' ... can anyone help?

NB the Lost_FollowUp field is a yes/no checkbox - could that have something
to do with it?

SELECT Screened.Site,

(Sum(IIf([Consent]=1 And [ArmAllocated]=1 And [Violations_Dropouts]=2,1,0))
Or Sum(IIf([Consent]=1 And [ArmAllocated]=1 And [Violations_Dropouts]=1 And
[Lost_FollowUp]=-1,1,0))) AS [AC Chosen Withdrawal]

FROM (Recruitment INNER JOIN Randomisation ON Recruitment.StudyNumber =
Randomisation.StudyNumber) INNER JOIN Screened ON Recruitment.StudyNumber =
Screened.StudyNumber
GROUP BY Screened.Site;
Click to expand...
Click to expand...
 
S

Stefan Hoffmann

hi Sergio,

try to start a new thread for a new question the next time.
SELECT MP06.TER, MP06.MEST
FROM MP06
WHERE (((MP06.TER) Like "11*") AND ((MP06.MEST) Not Like "11*") or
((MP06.TER) Like "15*") AND ((MP06.MEST) Not Like "15*") or ((MP06.TER)
Like "19*") AND ((MP06.MEST) Not Like "19*") );

But in fact those numbers 11, 15, 19 etc I need to take from a table AREA.
There are much more of those numbers.
Is there a way to do it?
Click to expand...
Try this:

SELECT MP06.TER, MP06.MEST
FROM MP06 INNER JOIN AREA
ON Left(MP06.TER, 2) = AREA.[yourField]
AND Left(MP06.MEST, 2) <> AREA.[yourField]



mfG
--> stefan <--
 
J

John Spencer

I don't see anything wrong in the expression as posted. Sometimes if you
copy and paste from the posting exraneous characters are introduced.
Try typing the expression in.

If that fails, try
Sum(IIf([Consent]=1 And [ArmAllocated]=1 And [Violations_Dropouts]=2),1,0)

If that works then try add the second set of conditions in.

....AHHHH I think I see the problem, I missed some parentheses.
Hopefully, I got it right this time.

Sum(IIf(([Consent]=1 And [ArmAllocated]=1 And [Violations_Dropouts]=2)
Or ([Consent]=1 And [ArmAllocated]=1 And [Violations_Dropouts]=1 And
[Lost_FollowUp]=-1),1,0)) AS [AC Chosen Withdrawal]

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Emelina said:
Thanks for this John, this is exactly the calculation i want to do. however,
the SQL you've kindly done below isn't working in my query - it's saying
there's the 'wrong number of arguments used with function in query
expression.... any ideas? Thanks for your time

John Spencer said:
Perhaps what you want is to count the number of instances where one set
of conditions or the other is true.

Sum(IIf([Consent]=1 And [ArmAllocated]=1 And [Violations_Dropouts]=2)
Or ([Consent]=1 And [ArmAllocated]=1 And [Violations_Dropouts]=1 And
[Lost_FollowUp]=-1),1,0) AS [AC Chosen Withdrawal]

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Emelina said:
I have a query as below and can't figure out what's wrong with it. It returns
only a '-1' if the conditions are true irrespective or how many cases it's
true in i.e. i made 2 records have the conditions of this query so it should
have returned '2' but it just returned '-1' ... can anyone help?

NB the Lost_FollowUp field is a yes/no checkbox - could that have something
to do with it?

SELECT Screened.Site,

(Sum(IIf([Consent]=1 And [ArmAllocated]=1 And [Violations_Dropouts]=2,1,0))
Or Sum(IIf([Consent]=1 And [ArmAllocated]=1 And [Violations_Dropouts]=1 And
[Lost_FollowUp]=-1,1,0))) AS [AC Chosen Withdrawal]

FROM (Recruitment INNER JOIN Randomisation ON Recruitment.StudyNumber =
Randomisation.StudyNumber) INNER JOIN Screened ON Recruitment.StudyNumber =
Screened.StudyNumber
GROUP BY Screened.Site;
Click to expand...
Click to expand...
Click to expand...
 
E

Emelina Bumsquash

This works a treat - thank you so much John!

John Spencer said:
I don't see anything wrong in the expression as posted. Sometimes if you
copy and paste from the posting exraneous characters are introduced.
Try typing the expression in.

If that fails, try
Sum(IIf([Consent]=1 And [ArmAllocated]=1 And [Violations_Dropouts]=2),1,0)

If that works then try add the second set of conditions in.

....AHHHH I think I see the problem, I missed some parentheses.
Hopefully, I got it right this time.

Sum(IIf(([Consent]=1 And [ArmAllocated]=1 And [Violations_Dropouts]=2)
Or ([Consent]=1 And [ArmAllocated]=1 And [Violations_Dropouts]=1 And
[Lost_FollowUp]=-1),1,0)) AS [AC Chosen Withdrawal]

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Emelina said:
Thanks for this John, this is exactly the calculation i want to do. however,
the SQL you've kindly done below isn't working in my query - it's saying
there's the 'wrong number of arguments used with function in query
expression.... any ideas? Thanks for your time

John Spencer said:
Perhaps what you want is to count the number of instances where one set
of conditions or the other is true.

Sum(IIf([Consent]=1 And [ArmAllocated]=1 And [Violations_Dropouts]=2)
Or ([Consent]=1 And [ArmAllocated]=1 And [Violations_Dropouts]=1 And
[Lost_FollowUp]=-1),1,0) AS [AC Chosen Withdrawal]

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Emelina Bumsquash wrote:
I have a query as below and can't figure out what's wrong with it. It returns
only a '-1' if the conditions are true irrespective or how many cases it's
true in i.e. i made 2 records have the conditions of this query so it should
have returned '2' but it just returned '-1' ... can anyone help?

NB the Lost_FollowUp field is a yes/no checkbox - could that have something
to do with it?

SELECT Screened.Site,

(Sum(IIf([Consent]=1 And [ArmAllocated]=1 And [Violations_Dropouts]=2,1,0))
Or Sum(IIf([Consent]=1 And [ArmAllocated]=1 And [Violations_Dropouts]=1 And
[Lost_FollowUp]=-1,1,0))) AS [AC Chosen Withdrawal]

FROM (Recruitment INNER JOIN Randomisation ON Recruitment.StudyNumber =
Randomisation.StudyNumber) INNER JOIN Screened ON Recruitment.StudyNumber =
Screened.StudyNumber
GROUP BY Screened.Site;
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

group and sort by month / site 8
sort by and group by month/year - please help! 2
What's wrong with this query?! 5
What's wrong with this query? 4
what's wrong with my query 2
Newbie, trouble with IIFstatement in SQL query 4
MS ACCESS query returns empty recordset from odbc linked sql server tables 3

Top