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

  • Thread starter Thread starter Emelina Bumsquash
  • Start date Start date
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;
 
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 <--
 
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
 
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...
 
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
'====================================================
 
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...
 
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 <--
 
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...
 
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...
 
Back
Top