Incorporating IF in an multiple aggregate query

[Guest]

I am trying to put an IF statement in my query. I need raw scores to be
adjusted to be between 0 and 1 if they aren't already. Here's what I want it
to do for 6 different fields:

If fieldrawscore > 1, then fieldrawscore = "1"
Else if fieldrawscore < 1, then fieldrawscore = "0"
Else fieldrawscore = fieldrawscore
End

I cannot figure out how to do this in my SQL expression (IIF isn't working
for me), and I am not savvy on VBA yet. (I'm not even sure how to incorporate
VBA within an existing query.)

I have an additional problem: my fieldrawscores that I want to use the IF on
are the result of another query that involves sums and other expressions.
Can I put the criteria in the same query that I build the expressions for the
fieldrawscores in? Or do I have to run a separate query just for the IF
criteria?

Here is the SQL for the query that contains the fieldrawscores that I need
to apply the criteria to:

SELECT BioRecon.brSampleDate,
(Sum([brTotalTaxa])-11)/25 AS [Total Taxa Raw Score],
(Sum([brEphemeropteraTaxa]))/5 AS [Ephemeroptera Taxa Raw Score],
(Sum([brTrichopteraTaxa]))/7 AS [Trichoptera Taxa Raw Score],
(Sum([brLongLivedTaxa]))/7 AS [Long Lived Taxa Raw Score],
(Sum([brClingerTaxa]))/8 AS [Clinger Taxa Raw Score],
(Sum([brSensitiveTaxa]))/9 AS [Sensitive Taxa Raw Score]
FROM BioRecon
INNER JOIN qryBioReconCountSummarybyDate ON
BioRecon.brSampleDate = qryBioReconCountSummarybyDate.brSampleDate
GROUP BY BioRecon.brSampleDate
ORDER BY BioRecon.brSampleDate;

Thank you!

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

To ease maintenance it would probably be better to place the IIf()
functions in another query.

Using "fieldrawscore = fieldrawscore" will return a Boolean value
(True). Your IF logic indicates that only 2 checks are required:

1. Is the value greater than 1
2. Is the value less than 1.

There is an implied conclusion that if the value doesn't satisfy either
of those 2 evaluations then it equals 1:

The value between <1 and >1 is 1.

Therefore, here is how to use IIf() on your data:

IIf(fieldrawscore > 1, 1, IIf(fieldrawscore < 1, 0, 1))

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQd155oechKqOuFEgEQLSnACg7d5ScuAA9mBWzN3guV7U/Fq0c1wAmwdD
Hk+Da/Pb1AS0IidhiBDsNMJl
=uG7P
-----END PGP SIGNATURE-----

 

[Guest]

I'm sorry. I miswrote my IF statement.

If fieldrawscore < 0, THEN fieldrawscore = 0

I have 3 possible out comes:
1. The value is between 0 and 1 and it should stay the same.
2. The value is greater than 1. It should be rounded to 1.
3. The value if less than 0. It should be rounded to 0.

I'm sorry for the confusion.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

To ease maintenance it would probably be better to place the IIf()
functions in another query.

Using "fieldrawscore = fieldrawscore" will return a Boolean value
(True). Your IF logic indicates that only 2 checks are required:

1. Is the value greater than 1
2. Is the value less than 1.

There is an implied conclusion that if the value doesn't satisfy either
of those 2 evaluations then it equals 1:

The value between <1 and >1 is 1.

Therefore, here is how to use IIf() on your data:

IIf(fieldrawscore > 1, 1, IIf(fieldrawscore < 1, 0, 1))

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQd155oechKqOuFEgEQLSnACg7d5ScuAA9mBWzN3guV7U/Fq0c1wAmwdD
Hk+Da/Pb1AS0IidhiBDsNMJl
=uG7P
-----END PGP SIGNATURE-----

I am trying to put an IF statement in my query. I need raw scores to be
adjusted to be between 0 and 1 if they aren't already. Here's what I want it
to do for 6 different fields:

If fieldrawscore > 1, then fieldrawscore = "1"
Else if fieldrawscore < 1, then fieldrawscore = "0"
Else fieldrawscore = fieldrawscore
End

I cannot figure out how to do this in my SQL expression (IIF isn't working
for me), and I am not savvy on VBA yet. (I'm not even sure how to incorporate
VBA within an existing query.)

I have an additional problem: my fieldrawscores that I want to use the IF on
are the result of another query that involves sums and other expressions.
Can I put the criteria in the same query that I build the expressions for the
fieldrawscores in? Or do I have to run a separate query just for the IF
criteria?

Here is the SQL for the query that contains the fieldrawscores that I need
to apply the criteria to:

SELECT BioRecon.brSampleDate,
(Sum([brTotalTaxa])-11)/25 AS [Total Taxa Raw Score],
(Sum([brEphemeropteraTaxa]))/5 AS [Ephemeroptera Taxa Raw Score],
(Sum([brTrichopteraTaxa]))/7 AS [Trichoptera Taxa Raw Score],
(Sum([brLongLivedTaxa]))/7 AS [Long Lived Taxa Raw Score],
(Sum([brClingerTaxa]))/8 AS [Clinger Taxa Raw Score],
(Sum([brSensitiveTaxa]))/9 AS [Sensitive Taxa Raw Score]
FROM BioRecon
INNER JOIN qryBioReconCountSummarybyDate ON
BioRecon.brSampleDate = qryBioReconCountSummarybyDate.brSampleDate
GROUP BY BioRecon.brSampleDate
ORDER BY BioRecon.brSampleDate;

Thank you!

 

[fredg]

I am trying to put an IF statement in my query. I need raw scores to be
adjusted to be between 0 and 1 if they aren't already. Here's what I want it
to do for 6 different fields:

If fieldrawscore > 1, then fieldrawscore = "1"
Else if fieldrawscore < 1, then fieldrawscore = "0"
Else fieldrawscore = fieldrawscore
End

I cannot figure out how to do this in my SQL expression (IIF isn't working
for me), and I am not savvy on VBA yet. (I'm not even sure how to incorporate
VBA within an existing query.)

*** snipped **

What do you mean IIf isn't working for you?
IIf is the easiest way to resolve your problem.

In your query:
AdjScore:IIf([FieldrawScore]>1,1,IIf([FieldrawScore]<1,0,[Fieldrawscore]))

Then use [AdjScore] in your report, form, etc.

 

[MGFoster]

That would change the IIf() statement to this:

IIf(fieldrawscore > 1, 1, IIf(fieldrawscore < 0, 0, fieldrawscore))

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

I'm sorry. I miswrote my IF statement.

If fieldrawscore < 0, THEN fieldrawscore = 0

I have 3 possible out comes:
1. The value is between 0 and 1 and it should stay the same.
2. The value is greater than 1. It should be rounded to 1.
3. The value if less than 0. It should be rounded to 0.

I'm sorry for the confusion.

:

< SNIP >

 

[Guest]

Thank you, fredg. That worked after I added an additional IIF expression for
IS Null.

Is there a reason that I cannot use a GROUP BY expression after the IIF and
FROM statements? I solved the problem by setting query properties to unique
values, but I would like to know why I get an error when trying to group by
date.

I am trying to put an IF statement in my query. I need raw scores to be
adjusted to be between 0 and 1 if they aren't already. Here's what I want it
to do for 6 different fields:

*** snipped **

What do you mean IIf isn't working for you?
IIf is the easiest way to resolve your problem.

In your query:
AdjScore:IIf([FieldrawScore]>1,1,IIf([FieldrawScore]<1,0,[Fieldrawscore]))

Then use [AdjScore] in your report, form, etc.