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
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)
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
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
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
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.
