Query SQL not working..

It seems to have brought back a few more records than I would have
originally though, totally approx. 15 or so (I thought it was more like 3 or
4). I'm trying to ascertain whether or not these are valid or not (telling
me I screwed up the query somewhere). They appear to be valid off hand
though. I think this just might do it.

Thanks for all your help on this Michel, and thank you very much for
pointing me in the right direction. I don't know the ins-and-outs of what
I've done, but I'm trying to learn it. Appreciate the help. Will let you
know the results.

--
Regards,
Zack Barresse, aka firefytr (MVP: Excel)


"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message
...
Hi,


exactly, assuming Monitoring_Well_Date.Well is your field that supplies
the GROUPs for the first query where you compute the stats, that is
exactly what I was having in mind.

Vanderghast, Access MVP

"Zack Barresse" wrote in message
...
Michel, I received results with this SQL ...


SELECT Monitoring_Well_Data.SampleDate, Monitoring_Well_Data.Well,
Monitoring_Well_Data.Temperature, Monitoring_Well_Data.pH,
Monitoring_Well_Data.Conductivity, Monitoring_Well_Data.N03,
Monitoring_Well_Data.SO4, Monitoring_Well_Data.TDS
FROM Monitoring_Well_Data INNER JOIN MW_CheckValues_StDev ON
Monitoring_Well_Data.Well = MW_CheckValues_StDev.Well
WHERE (((Monitoring_Well_Data.Temperature)[AvgOfTemperature]+[SdTemp3]))
OR
(((Monitoring_Well_Data.pH)[AvgOfpH]+[SdpH3])) OR

(((Monitoring_Well_Data.Conductivity)[AvgOfConductivity]+[SdCond3])) OR
(((Monitoring_Well_Data.N03)[AvgOfN03]+[SdNO33])) OR
(((Monitoring_Well_Data.SO4)[AvgOfSO4]+[SdSO43])) OR
(((Monitoring_Well_Data.TDS)[AvgOfTDS]+[SdTDS3]))
ORDER BY Monitoring_Well_Data.SampleDate;


does that look about right to you? They seem to look alright.

--
Regards,
Zack Barresse, aka firefytr (MVP: Excel)


"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message
...
Hi,


Have you tried to use NULL, not zero, as default value. The way
AVG(fieldname) works, it excludes the NULL, but it takes into account
the ZERO as legitimate values (for the computation of the MEAN and of
the Standard Deviation). And indeed AVG( null, null, 1, 3) returns
2; while AVG(0, 0, 1, 3) returns 1. (pseudo notation, AVG( ) works on a
field, not on a list of constants)

If you cannot use NULL as default, but are doomed to use the already
present 0, then something like:

AVG( iif( 0=N03, null, N03) )

can be used instead of

AVG(NO3)

and so on.


You have to make one query that computes the AVG and the SDev, then, in
another query, bring the original table, with its un-grouped data, and
the saved query. Make a join through the groups used to compute the
stats, then make the criteria to pin-point the data out of 3sigma.

In a total query, you can access to the group, ungrouped, or
un-aggregated data in the WHERE clause, or access to the group, or
aggregated, data in the HAVING clause. You cannot refer to an aggregate
in the WHERE clause, since the aggregation has not occurred yet, and you
cannot refer to the un-aggregated data in the HAVING clause, since any
individual has been "merged" in the aggregation (within the group). So,
you cannot use

x MEAN(x) + 3*SDEV(x)


in one (1) total query. That is why you need 2 queries, one of them
computing the stats and the other joining the computed stats with the
original un-aggregated data.


Note that you can do it in just one physical query, using a correlated
sub-query:

SELECT whatever
FROM myTable As a
WHERE x ( SELECT MEAN(b.x) + 3*SDEV(b.x)
FROM myTable As b
WHERE b.sample = a.sample
AND b.x 0)



where the inner select plays the role of the two queries approach, and
its inside where condition plays the role of the join. Basically, it is
the same idea, but expressed differently. In this case, on the other
hand, it is somehow easier to remove the unwanted zeros, but you pay for
a complexity (imho).


Hoping it may help,
Vanderghast, Access MVP

"Zack Barresse" wrote in message
...
Sure Duane, no problem. I have a table that tracks our Monitoring
Wells (small tubes which go into the groundwater where we pump out xx
gallons {generally 25-100} 4 times a year) and we need to report on
these wells. This all used to be in Excel where I could have easily
manipulated the data. I do have a report for these wells in AC that
works beautifully.

The problem comes into play when I want to check the latest sample
result(s) to see if they are within the DEQ limits, which they have set
as a simple calculation:

StDev * 3 + Avg

If the last sample for a specific well (checked on all wells) goes
above this figure then it is "red flagged", and I would like to show
all fields which match the criteria or spit out a report on it or
something.

The table is setup in similar fashion to that which was in Excel as it
was just ported over. So there are some fields that may or may not be
used, all result fields have a default value of 0. These are the
current fields in the table: ID, Well, SampleDate, Top_Casing*,
Water_Elevation*, Temperature, pH, Conductivity, Na, Ca, Mg, SAR, NO3,
TKN, COD, Cl, SO4, TDS, Notes. The two * fields will probably be
removed as they're not used and the Top Casing is in another table
associated with the Well name, so if I need it I can do a lookup or
query (pretty sure I can get that myself anyway).

Not all of these lab tests are actually done, but they were in the past
and they have data in them. The only results we have now are Temp, pH,
Cond, NO3, SO4, TDS & Notes. I don't know if that means I should make
two tables or keep those which are not sampled as a default value of 0.

Does this make more sense? Let me know if you need more information.
Thanks again Duane.

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM



"Duane Hookom" DuaneAtNoSpanHookomDotNet wrote in message
...
Those are your expressions from your first SQL view. Go back to your
first posting and you will see them.
Maybe you should tell us more about your table and what you are
attempting to do.
--
Duane Hookom
MS Access MVP

"Zack Barresse" wrote in message
...
Thanks for looking Duane. I run the query and I get a input box
titled "Enter Parameter Value". The StDevOfxxx and AvgOfxxx, what
are those? I really do not know what I'm doing here. Do those need
to be seperate queries?

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"Duane Hookom" DuaneAtNoSpanHookomDotNet wrote in message
...
Try this SQL which moves your criteria from "HAVING" to "WHERE":

SELECT Monitoring_Well_Data.SampleDate, Monitoring_Well_Data.Well
FROM Monitoring_Well_Data
WHERE ((([StDevOfTemperature]*3+[AvgOfTemperature])=[Temperature]))
OR
((([StDevOfpH]*3+[AvgOfpH])=[pH])) OR
((([StDevOfConductivity]*3+[AvgOfConductivity])=[Conductivity])) OR
((([StDevOfN03]*3+[AvgOfN03])=[N03])) OR
((([StDevOfSO4]*3+[AvgOfSO4])=[SO4])) OR
((([StDevOfTDS]*3+[AvgOfTDS])=[TDS]))
GROUP BY Monitoring_Well_Data.SampleDate, Monitoring_Well_Data.Well
ORDER BY Monitoring_Well_Data.SampleDate;

--
Duane Hookom
MS Access MVP

"Zack Barresse" wrote in message
...
Hello everyone, working with a database where I'm trying to produce
a query that will list all records that meet a certain condition.
Here is my SQL ...

SELECT Monitoring_Well_Data.SampleDate, Monitoring_Well_Data.Well
FROM Monitoring_Well_Data
GROUP BY Monitoring_Well_Data.SampleDate, Monitoring_Well_Data.Well
HAVING
((([StDevOfTemperature]*3+[AvgOfTemperature])=[Temperature])) OR
((([StDevOfpH]*3+[AvgOfpH])=[pH])) OR
((([StDevOfConductivity]*3+[AvgOfConductivity])=[Conductivity]))
OR
((([StDevOfN03]*3+[AvgOfN03])=[N03])) OR
((([StDevOfSO4]*3+[AvgOfSO4])=[SO4])) OR
((([StDevOfTDS]*3+[AvgOfTDS])=[TDS]))
ORDER BY Monitoring_Well_Data.SampleDate;

As you can tell, this is with the 'Monitoring_Well_Data' table.
The criteria is StDev * 3 + Avg, if any records meet or
exceed said criteria I want to show those records.

Anybody see anything wrong with this SQL statement? The error I
get is, "You tried to execute a query that does not include the
specified expression expression as part of an
aggregate function." I do not know what an aggregate function is
either.

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM

I think the extra values are stemming from the underlying query in the fact
that the Avg & StDev are looking at all values including zeros. As
mentioned earlier, that will skew the *real* results. What do you think
would be the best way to handle this? Should I try to quelch it in the
underlying query itself? Any insight on how to handle this would be
appreciated. Thanks for all the help thus far.

--
Regards,
Zack Barresse, aka firefytr (MVP: Excel)


"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message
...
Hi,


exactly, assuming Monitoring_Well_Date.Well is your field that supplies
the GROUPs for the first query where you compute the stats, that is
exactly what I was having in mind.

Vanderghast, Access MVP

"Zack Barresse" wrote in message
...
Michel, I received results with this SQL ...


SELECT Monitoring_Well_Data.SampleDate, Monitoring_Well_Data.Well,
Monitoring_Well_Data.Temperature, Monitoring_Well_Data.pH,
Monitoring_Well_Data.Conductivity, Monitoring_Well_Data.N03,
Monitoring_Well_Data.SO4, Monitoring_Well_Data.TDS
FROM Monitoring_Well_Data INNER JOIN MW_CheckValues_StDev ON
Monitoring_Well_Data.Well = MW_CheckValues_StDev.Well
WHERE (((Monitoring_Well_Data.Temperature)[AvgOfTemperature]+[SdTemp3]))
OR
(((Monitoring_Well_Data.pH)[AvgOfpH]+[SdpH3])) OR

(((Monitoring_Well_Data.Conductivity)[AvgOfConductivity]+[SdCond3])) OR
(((Monitoring_Well_Data.N03)[AvgOfN03]+[SdNO33])) OR
(((Monitoring_Well_Data.SO4)[AvgOfSO4]+[SdSO43])) OR
(((Monitoring_Well_Data.TDS)[AvgOfTDS]+[SdTDS3]))
ORDER BY Monitoring_Well_Data.SampleDate;


does that look about right to you? They seem to look alright.

--
Regards,
Zack Barresse, aka firefytr (MVP: Excel)


"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message
...
Hi,


Have you tried to use NULL, not zero, as default value. The way
AVG(fieldname) works, it excludes the NULL, but it takes into account
the ZERO as legitimate values (for the computation of the MEAN and of
the Standard Deviation). And indeed AVG( null, null, 1, 3) returns
2; while AVG(0, 0, 1, 3) returns 1. (pseudo notation, AVG( ) works on a
field, not on a list of constants)

If you cannot use NULL as default, but are doomed to use the already
present 0, then something like:

AVG( iif( 0=N03, null, N03) )

can be used instead of

AVG(NO3)

and so on.


You have to make one query that computes the AVG and the SDev, then, in
another query, bring the original table, with its un-grouped data, and
the saved query. Make a join through the groups used to compute the
stats, then make the criteria to pin-point the data out of 3sigma.

In a total query, you can access to the group, ungrouped, or
un-aggregated data in the WHERE clause, or access to the group, or
aggregated, data in the HAVING clause. You cannot refer to an aggregate
in the WHERE clause, since the aggregation has not occurred yet, and you
cannot refer to the un-aggregated data in the HAVING clause, since any
individual has been "merged" in the aggregation (within the group). So,
you cannot use

x MEAN(x) + 3*SDEV(x)


in one (1) total query. That is why you need 2 queries, one of them
computing the stats and the other joining the computed stats with the
original un-aggregated data.


Note that you can do it in just one physical query, using a correlated
sub-query:

SELECT whatever
FROM myTable As a
WHERE x ( SELECT MEAN(b.x) + 3*SDEV(b.x)
FROM myTable As b
WHERE b.sample = a.sample
AND b.x 0)



where the inner select plays the role of the two queries approach, and
its inside where condition plays the role of the join. Basically, it is
the same idea, but expressed differently. In this case, on the other
hand, it is somehow easier to remove the unwanted zeros, but you pay for
a complexity (imho).


Hoping it may help,
Vanderghast, Access MVP

"Zack Barresse" wrote in message
...
Sure Duane, no problem. I have a table that tracks our Monitoring
Wells (small tubes which go into the groundwater where we pump out xx
gallons {generally 25-100} 4 times a year) and we need to report on
these wells. This all used to be in Excel where I could have easily
manipulated the data. I do have a report for these wells in AC that
works beautifully.

The problem comes into play when I want to check the latest sample
result(s) to see if they are within the DEQ limits, which they have set
as a simple calculation:

StDev * 3 + Avg

If the last sample for a specific well (checked on all wells) goes
above this figure then it is "red flagged", and I would like to show
all fields which match the criteria or spit out a report on it or
something.

The table is setup in similar fashion to that which was in Excel as it
was just ported over. So there are some fields that may or may not be
used, all result fields have a default value of 0. These are the
current fields in the table: ID, Well, SampleDate, Top_Casing*,
Water_Elevation*, Temperature, pH, Conductivity, Na, Ca, Mg, SAR, NO3,
TKN, COD, Cl, SO4, TDS, Notes. The two * fields will probably be
removed as they're not used and the Top Casing is in another table
associated with the Well name, so if I need it I can do a lookup or
query (pretty sure I can get that myself anyway).

Not all of these lab tests are actually done, but they were in the past
and they have data in them. The only results we have now are Temp, pH,
Cond, NO3, SO4, TDS & Notes. I don't know if that means I should make
two tables or keep those which are not sampled as a default value of 0.

Does this make more sense? Let me know if you need more information.
Thanks again Duane.

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM



"Duane Hookom" DuaneAtNoSpanHookomDotNet wrote in message
...
Those are your expressions from your first SQL view. Go back to your
first posting and you will see them.
Maybe you should tell us more about your table and what you are
attempting to do.
--
Duane Hookom
MS Access MVP

"Zack Barresse" wrote in message
...
Thanks for looking Duane. I run the query and I get a input box
titled "Enter Parameter Value". The StDevOfxxx and AvgOfxxx, what
are those? I really do not know what I'm doing here. Do those need
to be seperate queries?

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"Duane Hookom" DuaneAtNoSpanHookomDotNet wrote in message
...
Try this SQL which moves your criteria from "HAVING" to "WHERE":

SELECT Monitoring_Well_Data.SampleDate, Monitoring_Well_Data.Well
FROM Monitoring_Well_Data
WHERE ((([StDevOfTemperature]*3+[AvgOfTemperature])=[Temperature]))
OR
((([StDevOfpH]*3+[AvgOfpH])=[pH])) OR
((([StDevOfConductivity]*3+[AvgOfConductivity])=[Conductivity])) OR
((([StDevOfN03]*3+[AvgOfN03])=[N03])) OR
((([StDevOfSO4]*3+[AvgOfSO4])=[SO4])) OR
((([StDevOfTDS]*3+[AvgOfTDS])=[TDS]))
GROUP BY Monitoring_Well_Data.SampleDate, Monitoring_Well_Data.Well
ORDER BY Monitoring_Well_Data.SampleDate;

--
Duane Hookom
MS Access MVP

"Zack Barresse" wrote in message
...
Hello everyone, working with a database where I'm trying to produce
a query that will list all records that meet a certain condition.
Here is my SQL ...

SELECT Monitoring_Well_Data.SampleDate, Monitoring_Well_Data.Well
FROM Monitoring_Well_Data
GROUP BY Monitoring_Well_Data.SampleDate, Monitoring_Well_Data.Well
HAVING
((([StDevOfTemperature]*3+[AvgOfTemperature])=[Temperature])) OR
((([StDevOfpH]*3+[AvgOfpH])=[pH])) OR
((([StDevOfConductivity]*3+[AvgOfConductivity])=[Conductivity]))
OR
((([StDevOfN03]*3+[AvgOfN03])=[N03])) OR
((([StDevOfSO4]*3+[AvgOfSO4])=[SO4])) OR
((([StDevOfTDS]*3+[AvgOfTDS])=[TDS]))
ORDER BY Monitoring_Well_Data.SampleDate;

As you can tell, this is with the 'Monitoring_Well_Data' table.
The criteria is StDev * 3 + Avg, if any records meet or
exceed said criteria I want to show those records.

Anybody see anything wrong with this SQL statement? The error I
get is, "You tried to execute a query that does not include the
specified expression expression as part of an
aggregate function." I do not know what an aggregate function is
either.

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM

Hi,


the preferred solution would be to replace these 0 by NULL:

UPDATE backupTable
SET NO3=null WHERE NO3 = 0


You have to update each fields individually, ie, for SO4, you need another
query:

UPDATE backupTable
SET SO4 = null WHERE SO4 = 0

and so on, for each required field,

and see if that solves the problem. If so, then:
either make a permanent update, and case is solved;
either use the iif formulation, based on existing un-updated data, like:

AVG( iif( NO3=0, null, NO3) )

where you used AVG( NO3 ), and so on for SDev, and so on for AVG( SO4 ),
etc.


Hoping it may help,
Vanderghast, Access MVP



"Zack Barresse" wrote in message
...
I think the extra values are stemming from the underlying query in the fact
that the Avg & StDev are looking at all values including zeros. As
mentioned earlier, that will skew the *real* results. What do you think
would be the best way to handle this? Should I try to quelch it in the
underlying query itself? Any insight on how to handle this would be
appreciated. Thanks for all the help thus far.

--
Regards,
Zack Barresse, aka firefytr (MVP: Excel)


"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message
...
Hi,


exactly, assuming Monitoring_Well_Date.Well is your field that supplies
the GROUPs for the first query where you compute the stats, that is
exactly what I was having in mind.

Vanderghast, Access MVP

"Zack Barresse" wrote in message
...
Michel, I received results with this SQL ...


SELECT Monitoring_Well_Data.SampleDate, Monitoring_Well_Data.Well,
Monitoring_Well_Data.Temperature, Monitoring_Well_Data.pH,
Monitoring_Well_Data.Conductivity, Monitoring_Well_Data.N03,
Monitoring_Well_Data.SO4, Monitoring_Well_Data.TDS
FROM Monitoring_Well_Data INNER JOIN MW_CheckValues_StDev ON
Monitoring_Well_Data.Well = MW_CheckValues_StDev.Well
WHERE
(((Monitoring_Well_Data.Temperature)[AvgOfTemperature]+[SdTemp3])) OR
(((Monitoring_Well_Data.pH)[AvgOfpH]+[SdpH3])) OR

(((Monitoring_Well_Data.Conductivity)[AvgOfConductivity]+[SdCond3])) OR
(((Monitoring_Well_Data.N03)[AvgOfN03]+[SdNO33])) OR
(((Monitoring_Well_Data.SO4)[AvgOfSO4]+[SdSO43])) OR
(((Monitoring_Well_Data.TDS)[AvgOfTDS]+[SdTDS3]))
ORDER BY Monitoring_Well_Data.SampleDate;


does that look about right to you? They seem to look alright.

--
Regards,
Zack Barresse, aka firefytr (MVP: Excel)


"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message
...
Hi,


Have you tried to use NULL, not zero, as default value. The way
AVG(fieldname) works, it excludes the NULL, but it takes into account
the ZERO as legitimate values (for the computation of the MEAN and of
the Standard Deviation). And indeed AVG( null, null, 1, 3) returns
2; while AVG(0, 0, 1, 3) returns 1. (pseudo notation, AVG( ) works on
a field, not on a list of constants)

If you cannot use NULL as default, but are doomed to use the already
present 0, then something like:

AVG( iif( 0=N03, null, N03) )

can be used instead of

AVG(NO3)

and so on.


You have to make one query that computes the AVG and the SDev, then, in
another query, bring the original table, with its un-grouped data, and
the saved query. Make a join through the groups used to compute the
stats, then make the criteria to pin-point the data out of 3sigma.

In a total query, you can access to the group, ungrouped, or
un-aggregated data in the WHERE clause, or access to the group, or
aggregated, data in the HAVING clause. You cannot refer to an aggregate
in the WHERE clause, since the aggregation has not occurred yet, and
you cannot refer to the un-aggregated data in the HAVING clause, since
any individual has been "merged" in the aggregation (within the group).
So, you cannot use

x MEAN(x) + 3*SDEV(x)


in one (1) total query. That is why you need 2 queries, one of them
computing the stats and the other joining the computed stats with the
original un-aggregated data.


Note that you can do it in just one physical query, using a correlated
sub-query:

SELECT whatever
FROM myTable As a
WHERE x ( SELECT MEAN(b.x) + 3*SDEV(b.x)
FROM myTable As b
WHERE b.sample = a.sample
AND b.x 0)



where the inner select plays the role of the two queries approach, and
its inside where condition plays the role of the join. Basically, it is
the same idea, but expressed differently. In this case, on the other
hand, it is somehow easier to remove the unwanted zeros, but you pay
for a complexity (imho).


Hoping it may help,
Vanderghast, Access MVP

"Zack Barresse" wrote in message
...
Sure Duane, no problem. I have a table that tracks our Monitoring
Wells (small tubes which go into the groundwater where we pump out xx
gallons {generally 25-100} 4 times a year) and we need to report on
these wells. This all used to be in Excel where I could have easily
manipulated the data. I do have a report for these wells in AC that
works beautifully.

The problem comes into play when I want to check the latest sample
result(s) to see if they are within the DEQ limits, which they have
set as a simple calculation:

StDev * 3 + Avg

If the last sample for a specific well (checked on all wells) goes
above this figure then it is "red flagged", and I would like to show
all fields which match the criteria or spit out a report on it or
something.

The table is setup in similar fashion to that which was in Excel as it
was just ported over. So there are some fields that may or may not be
used, all result fields have a default value of 0. These are the
current fields in the table: ID, Well, SampleDate, Top_Casing*,
Water_Elevation*, Temperature, pH, Conductivity, Na, Ca, Mg, SAR, NO3,
TKN, COD, Cl, SO4, TDS, Notes. The two * fields will probably be
removed as they're not used and the Top Casing is in another table
associated with the Well name, so if I need it I can do a lookup or
query (pretty sure I can get that myself anyway).

Not all of these lab tests are actually done, but they were in the
past and they have data in them. The only results we have now are
Temp, pH, Cond, NO3, SO4, TDS & Notes. I don't know if that means I
should make two tables or keep those which are not sampled as a
default value of 0.

Does this make more sense? Let me know if you need more information.
Thanks again Duane.

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM



"Duane Hookom" DuaneAtNoSpanHookomDotNet wrote in message
...
Those are your expressions from your first SQL view. Go back to your
first posting and you will see them.
Maybe you should tell us more about your table and what you are
attempting to do.
--
Duane Hookom
MS Access MVP

"Zack Barresse" wrote in message
...
Thanks for looking Duane. I run the query and I get a input box
titled "Enter Parameter Value". The StDevOfxxx and AvgOfxxx, what
are those? I really do not know what I'm doing here. Do those need
to be seperate queries?

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"Duane Hookom" DuaneAtNoSpanHookomDotNet wrote in message
...
Try this SQL which moves your criteria from "HAVING" to "WHERE":

SELECT Monitoring_Well_Data.SampleDate, Monitoring_Well_Data.Well
FROM Monitoring_Well_Data
WHERE
((([StDevOfTemperature]*3+[AvgOfTemperature])=[Temperature])) OR
((([StDevOfpH]*3+[AvgOfpH])=[pH])) OR
((([StDevOfConductivity]*3+[AvgOfConductivity])=[Conductivity]))
OR
((([StDevOfN03]*3+[AvgOfN03])=[N03])) OR
((([StDevOfSO4]*3+[AvgOfSO4])=[SO4])) OR
((([StDevOfTDS]*3+[AvgOfTDS])=[TDS]))
GROUP BY Monitoring_Well_Data.SampleDate, Monitoring_Well_Data.Well
ORDER BY Monitoring_Well_Data.SampleDate;

--
Duane Hookom
MS Access MVP

"Zack Barresse" wrote in message
...
Hello everyone, working with a database where I'm trying to
produce a query that will list all records that meet a certain
condition. Here is my SQL ...

SELECT Monitoring_Well_Data.SampleDate, Monitoring_Well_Data.Well
FROM Monitoring_Well_Data
GROUP BY Monitoring_Well_Data.SampleDate,
Monitoring_Well_Data.Well
HAVING
((([StDevOfTemperature]*3+[AvgOfTemperature])=[Temperature])) OR
((([StDevOfpH]*3+[AvgOfpH])=[pH])) OR
((([StDevOfConductivity]*3+[AvgOfConductivity])=[Conductivity]))
OR
((([StDevOfN03]*3+[AvgOfN03])=[N03])) OR
((([StDevOfSO4]*3+[AvgOfSO4])=[SO4])) OR
((([StDevOfTDS]*3+[AvgOfTDS])=[TDS]))
ORDER BY Monitoring_Well_Data.SampleDate;

As you can tell, this is with the 'Monitoring_Well_Data' table.
The criteria is StDev * 3 + Avg, if any records meet or
exceed said criteria I want to show those records.

Anybody see anything wrong with this SQL statement? The error I
get is, "You tried to execute a query that does not include the
specified expression expression as part of an
aggregate function." I do not know what an aggregate function is
either.

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM

Works great Michel. Thank you!

Is there a way to do them all at once? Not so familiar with writing SQL,
how do I string multiples together? Or do you just recommend I go through
one at a time? If this works, I have a few other tables that need it as
well.

--
Regards,
Zack Barresse, aka firefytr (MVP: Excel)


"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message
...
Hi,


the preferred solution would be to replace these 0 by NULL:

UPDATE backupTable
SET NO3=null WHERE NO3 = 0


You have to update each fields individually, ie, for SO4, you need another
query:

UPDATE backupTable
SET SO4 = null WHERE SO4 = 0

and so on, for each required field,

and see if that solves the problem. If so, then:
either make a permanent update, and case is solved;
either use the iif formulation, based on existing un-updated data, like:

AVG( iif( NO3=0, null, NO3) )

where you used AVG( NO3 ), and so on for SDev, and so on for AVG( SO4 ),
etc.


Hoping it may help,
Vanderghast, Access MVP



"Zack Barresse" wrote in message
...
I think the extra values are stemming from the underlying query in the
fact that the Avg & StDev are looking at all values including zeros. As
mentioned earlier, that will skew the *real* results. What do you think
would be the best way to handle this? Should I try to quelch it in the
underlying query itself? Any insight on how to handle this would be
appreciated. Thanks for all the help thus far.

--
Regards,
Zack Barresse, aka firefytr (MVP: Excel)


"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message
...
Hi,


exactly, assuming Monitoring_Well_Date.Well is your field that supplies
the GROUPs for the first query where you compute the stats, that is
exactly what I was having in mind.

Vanderghast, Access MVP

"Zack Barresse" wrote in message
...
Michel, I received results with this SQL ...


SELECT Monitoring_Well_Data.SampleDate, Monitoring_Well_Data.Well,
Monitoring_Well_Data.Temperature, Monitoring_Well_Data.pH,
Monitoring_Well_Data.Conductivity, Monitoring_Well_Data.N03,
Monitoring_Well_Data.SO4, Monitoring_Well_Data.TDS
FROM Monitoring_Well_Data INNER JOIN MW_CheckValues_StDev ON
Monitoring_Well_Data.Well = MW_CheckValues_StDev.Well
WHERE
(((Monitoring_Well_Data.Temperature)[AvgOfTemperature]+[SdTemp3])) OR
(((Monitoring_Well_Data.pH)[AvgOfpH]+[SdpH3])) OR

(((Monitoring_Well_Data.Conductivity)[AvgOfConductivity]+[SdCond3]))
OR
(((Monitoring_Well_Data.N03)[AvgOfN03]+[SdNO33])) OR
(((Monitoring_Well_Data.SO4)[AvgOfSO4]+[SdSO43])) OR
(((Monitoring_Well_Data.TDS)[AvgOfTDS]+[SdTDS3]))
ORDER BY Monitoring_Well_Data.SampleDate;


does that look about right to you? They seem to look alright.

--
Regards,
Zack Barresse, aka firefytr (MVP: Excel)


"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message
...
Hi,


Have you tried to use NULL, not zero, as default value. The way
AVG(fieldname) works, it excludes the NULL, but it takes into account
the ZERO as legitimate values (for the computation of the MEAN and of
the Standard Deviation). And indeed AVG( null, null, 1, 3) returns
2; while AVG(0, 0, 1, 3) returns 1. (pseudo notation, AVG( ) works on
a field, not on a list of constants)

If you cannot use NULL as default, but are doomed to use the already
present 0, then something like:

AVG( iif( 0=N03, null, N03) )

can be used instead of

AVG(NO3)

and so on.


You have to make one query that computes the AVG and the SDev, then,
in another query, bring the original table, with its un-grouped data,
and the saved query. Make a join through the groups used to compute
the stats, then make the criteria to pin-point the data out of 3sigma.

In a total query, you can access to the group, ungrouped, or
un-aggregated data in the WHERE clause, or access to the group, or
aggregated, data in the HAVING clause. You cannot refer to an
aggregate in the WHERE clause, since the aggregation has not occurred
yet, and you cannot refer to the un-aggregated data in the HAVING
clause, since any individual has been "merged" in the aggregation
(within the group). So, you cannot use

x MEAN(x) + 3*SDEV(x)


in one (1) total query. That is why you need 2 queries, one of them
computing the stats and the other joining the computed stats with the
original un-aggregated data.


Note that you can do it in just one physical query, using a correlated
sub-query:

SELECT whatever
FROM myTable As a
WHERE x ( SELECT MEAN(b.x) + 3*SDEV(b.x)
FROM myTable As b
WHERE b.sample = a.sample
AND b.x 0)



where the inner select plays the role of the two queries approach, and
its inside where condition plays the role of the join. Basically, it
is the same idea, but expressed differently. In this case, on the
other hand, it is somehow easier to remove the unwanted zeros, but you
pay for a complexity (imho).


Hoping it may help,
Vanderghast, Access MVP

"Zack Barresse" wrote in message
...
Sure Duane, no problem. I have a table that tracks our Monitoring
Wells (small tubes which go into the groundwater where we pump out xx
gallons {generally 25-100} 4 times a year) and we need to report on
these wells. This all used to be in Excel where I could have easily
manipulated the data. I do have a report for these wells in AC that
works beautifully.

The problem comes into play when I want to check the latest sample
result(s) to see if they are within the DEQ limits, which they have
set as a simple calculation:

StDev * 3 + Avg

If the last sample for a specific well (checked on all wells) goes
above this figure then it is "red flagged", and I would like to show
all fields which match the criteria or spit out a report on it or
something.

The table is setup in similar fashion to that which was in Excel as
it was just ported over. So there are some fields that may or may
not be used, all result fields have a default value of 0. These are
the current fields in the table: ID, Well, SampleDate, Top_Casing*,
Water_Elevation*, Temperature, pH, Conductivity, Na, Ca, Mg, SAR,
NO3, TKN, COD, Cl, SO4, TDS, Notes. The two * fields will probably
be removed as they're not used and the Top Casing is in another table
associated with the Well name, so if I need it I can do a lookup or
query (pretty sure I can get that myself anyway).

Not all of these lab tests are actually done, but they were in the
past and they have data in them. The only results we have now are
Temp, pH, Cond, NO3, SO4, TDS & Notes. I don't know if that means I
should make two tables or keep those which are not sampled as a
default value of 0.

Does this make more sense? Let me know if you need more information.
Thanks again Duane.

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM



"Duane Hookom" DuaneAtNoSpanHookomDotNet wrote in message
...
Those are your expressions from your first SQL view. Go back to your
first posting and you will see them.
Maybe you should tell us more about your table and what you are
attempting to do.
--
Duane Hookom
MS Access MVP

"Zack Barresse" wrote in message
...
Thanks for looking Duane. I run the query and I get a input box
titled "Enter Parameter Value". The StDevOfxxx and AvgOfxxx, what
are those? I really do not know what I'm doing here. Do those need
to be seperate queries?

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"Duane Hookom" DuaneAtNoSpanHookomDotNet wrote in message
...
Try this SQL which moves your criteria from "HAVING" to "WHERE":

SELECT Monitoring_Well_Data.SampleDate, Monitoring_Well_Data.Well
FROM Monitoring_Well_Data
WHERE
((([StDevOfTemperature]*3+[AvgOfTemperature])=[Temperature])) OR
((([StDevOfpH]*3+[AvgOfpH])=[pH])) OR
((([StDevOfConductivity]*3+[AvgOfConductivity])=[Conductivity]))
OR
((([StDevOfN03]*3+[AvgOfN03])=[N03])) OR
((([StDevOfSO4]*3+[AvgOfSO4])=[SO4])) OR
((([StDevOfTDS]*3+[AvgOfTDS])=[TDS]))
GROUP BY Monitoring_Well_Data.SampleDate,
Monitoring_Well_Data.Well
ORDER BY Monitoring_Well_Data.SampleDate;

--
Duane Hookom
MS Access MVP

"Zack Barresse" wrote in message
...
Hello everyone, working with a database where I'm trying to
produce a query that will list all records that meet a certain
condition. Here is my SQL ...

SELECT Monitoring_Well_Data.SampleDate, Monitoring_Well_Data.Well
FROM Monitoring_Well_Data
GROUP BY Monitoring_Well_Data.SampleDate,
Monitoring_Well_Data.Well
HAVING
((([StDevOfTemperature]*3+[AvgOfTemperature])=[Temperature])) OR
((([StDevOfpH]*3+[AvgOfpH])=[pH])) OR
((([StDevOfConductivity]*3+[AvgOfConductivity])=[Conductivity]))
OR
((([StDevOfN03]*3+[AvgOfN03])=[N03])) OR
((([StDevOfSO4]*3+[AvgOfSO4])=[SO4])) OR
((([StDevOfTDS]*3+[AvgOfTDS])=[TDS]))
ORDER BY Monitoring_Well_Data.SampleDate;

As you can tell, this is with the 'Monitoring_Well_Data' table.
The criteria is StDev * 3 + Avg, if any records meet or
exceed said criteria I want to show those records.

Anybody see anything wrong with this SQL statement? The error I
get is, "You tried to execute a query that does not include the
specified expression expression as part of an
aggregate function." I do not know what an aggregate function is
either.

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM

Hi,


Yes, but make tests before. Something like:

UPDATE backup
SET NO3=iif(NO3=0, null, NO3),
SO4=iif(SO4=0, null, SO4)


add all the required fields, and no WHERE clause! That means it could be
SLOW, and could take some time, be sure to not close the application (doing
so, in the middle of an update may corrupt your whole database) until it
"returns" to you and that you can edit the table to see its modifications.

You have to do it for each table, on the other hand.

And change the tables design default values to NULL, to avoid having to do
it again and again :-)


Hoping it may help,
Vanderghast, Access MVP


"Zack Barresse" wrote in message
...
Works great Michel. Thank you!

Is there a way to do them all at once? Not so familiar with writing SQL,
how do I string multiples together? Or do you just recommend I go through
one at a time? If this works, I have a few other tables that need it as
well.

--
Regards,
Zack Barresse, aka firefytr (MVP: Excel)

The OP could use a where clause that might speed this up.

UPDATE Backup
Set NO3 = IIF(NO3=0,Null,NO3),
SO4=IIF(SO4=0,Null,SO4),
...
WHERE NO3 = 0 Or SO4=0 Or ...

If there aren't a lot of fields with zeroes this could be faster (especially
if the fields happen to be indexed)


"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message
...
Hi,


Yes, but make tests before. Something like:

UPDATE backup
SET NO3=iif(NO3=0, null, NO3),
SO4=iif(SO4=0, null, SO4)


add all the required fields, and no WHERE clause! That means it could be
SLOW, and could take some time, be sure to not close the application
(doing so, in the middle of an update may corrupt your whole database)
until it "returns" to you and that you can edit the table to see its
modifications.

You have to do it for each table, on the other hand.

And change the tables design default values to NULL, to avoid having to do
it again and again :-)


Hoping it may help,
Vanderghast, Access MVP


"Zack Barresse" wrote in message
...
Works great Michel. Thank you!

Is there a way to do them all at once? Not so familiar with writing SQL,
how do I string multiples together? Or do you just recommend I go
through one at a time? If this works, I have a few other tables that
need it as well.

--
Regards,
Zack Barresse, aka firefytr (MVP: Excel)

Thank you Michel, that's the syntax I was looking for, much appreciated. I
have taken out the default value of 0 and left it blank (I'm hoping that is
what you meant by leave default as null). Thanks for your time on this one.

--
Regards,
Zack Barresse, aka firefytr (MVP: Excel)


"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message
...
Hi,


Yes, but make tests before. Something like:

UPDATE backup
SET NO3=iif(NO3=0, null, NO3),
SO4=iif(SO4=0, null, SO4)


add all the required fields, and no WHERE clause! That means it could be
SLOW, and could take some time, be sure to not close the application
(doing so, in the middle of an update may corrupt your whole database)
until it "returns" to you and that you can edit the table to see its
modifications.

You have to do it for each table, on the other hand.

And change the tables design default values to NULL, to avoid having to do
it again and again :-)


Hoping it may help,
Vanderghast, Access MVP


"Zack Barresse" wrote in message
...
Works great Michel. Thank you!

Is there a way to do them all at once? Not so familiar with writing SQL,
how do I string multiples together? Or do you just recommend I go
through one at a time? If this works, I have a few other tables that
need it as well.

--
Regards,
Zack Barresse, aka firefytr (MVP: Excel)

Hi,


Yes, leaving the default value blank, in this case, is understood as a NULL
value. (Even if technically an empty string, which is a string with no
character in it, differs from a NULL value, which is more a "unavailable",
"unknown" meta-information, it is common to represent both, empty string,
and NULL, graphically, as a "blank". The problem is often the reverse: I
have a blank, is it an empty string or is it a NULL? Access generally
decides in favor of a NULL, but VB6, as example, will decide in favor of an
empty string. So, since you are within Access, and the field is numerical,
it cannot be an empty string, so, in this case, it is understood as a NULL,
definitively.)


Vanderghast, Access MVP


"Zack Barresse" wrote in message
...
Thank you Michel, that's the syntax I was looking for, much appreciated.
I have taken out the default value of 0 and left it blank (I'm hoping that
is what you meant by leave default as null). Thanks for your time on this
one.

--
Regards,
Zack Barresse, aka firefytr (MVP: Excel)


"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message
...
Hi,


Yes, but make tests before. Something like:

UPDATE backup
SET NO3=iif(NO3=0, null, NO3),
SO4=iif(SO4=0, null, SO4)


add all the required fields, and no WHERE clause! That means it could be
SLOW, and could take some time, be sure to not close the application
(doing so, in the middle of an update may corrupt your whole database)
until it "returns" to you and that you can edit the table to see its
modifications.

You have to do it for each table, on the other hand.

And change the tables design default values to NULL, to avoid having to
do it again and again :-)


Hoping it may help,
Vanderghast, Access MVP


"Zack Barresse" wrote in message
...
Works great Michel. Thank you!

Is there a way to do them all at once? Not so familiar with writing
SQL, how do I string multiples together? Or do you just recommend I go
through one at a time? If this works, I have a few other tables that
need it as well.

--
Regards,
Zack Barresse, aka firefytr (MVP: Excel)

Thanks very much for the explanation! I had figured it for as much. Quite
different from other programs I've run. Thanks for taking the time Michel.
I greatly appreciate your help.

--
Regards,
Zack Barresse, aka firefytr (MVP: Excel)


"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message
...
Hi,


Yes, leaving the default value blank, in this case, is understood as a
NULL value. (Even if technically an empty string, which is a string with
no character in it, differs from a NULL value, which is more a
"unavailable", "unknown" meta-information, it is common to represent both,
empty string, and NULL, graphically, as a "blank". The problem is often
the reverse: I have a blank, is it an empty string or is it a NULL?
Access generally decides in favor of a NULL, but VB6, as example, will
decide in favor of an empty string. So, since you are within Access, and
the field is numerical, it cannot be an empty string, so, in this case, it
is understood as a NULL, definitively.)


Vanderghast, Access MVP


"Zack Barresse" wrote in message
...
Thank you Michel, that's the syntax I was looking for, much appreciated.
I have taken out the default value of 0 and left it blank (I'm hoping
that is what you meant by leave default as null). Thanks for your time
on this one.

--
Regards,
Zack Barresse, aka firefytr (MVP: Excel)


"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message
...
Hi,


Yes, but make tests before. Something like:

UPDATE backup
SET NO3=iif(NO3=0, null, NO3),
SO4=iif(SO4=0, null, SO4)


add all the required fields, and no WHERE clause! That means it could be
SLOW, and could take some time, be sure to not close the application
(doing so, in the middle of an update may corrupt your whole database)
until it "returns" to you and that you can edit the table to see its
modifications.

You have to do it for each table, on the other hand.

And change the tables design default values to NULL, to avoid having to
do it again and again :-)


Hoping it may help,
Vanderghast, Access MVP


"Zack Barresse" wrote in message
...
Works great Michel. Thank you!

Is there a way to do them all at once? Not so familiar with writing
SQL, how do I string multiples together? Or do you just recommend I go
through one at a time? If this works, I have a few other tables that
need it as well.

--
Regards,
Zack Barresse, aka firefytr (MVP: Excel)