Solved: Query SQL not working

[Zack Barresse]

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

[Norie]

Zack

Does this actually need to be a aggregrate query?

You don't actually appear to be aggregrating anything.

Can you attach some sample data so we can test?

[Zack Barresse]

Not too sure what an aggregate query is. Zipped database is attached with the tables/queries used.

[Norie]

Zack

An aggregrate query is one where you get sums, averages etc.

I'm a little confused by your query, where are StDevOfTemperature, AvgOfTemperature meant to be coming from?

[Zack Barresse]

Not really sure how to answer that Norie. I used the expression builder. Hmm, I may know, I did have those same calculated fields in there (query) twice, using a Totals query and changed the Total to StDev and another one to Avg.

Does that help?

[Norie]

What I think you need is another query that only returns the standard deviations and averages.

You could then use that in another query in the same way you would a table.

I'll have another look at the attachment later - I tried a few things but all I managed to do was hang Access.:)

I'm not sure but I think that had something to do with StDev, that's not something I've used often (ever?) in Access.

[Zack Barresse]

Thanks for looking Norie. Will look forward to your (and any other) input on this. :yes

[stanl]

What I think you need is another query that only returns the standard deviations and averages.

You could then use that in another query in the same way you would a table.


Maybe a sub-select?? The query has 6 rules and no way to tell if a return value meets one or several of them. I'm having a day from hell trying to set up Vonage (I'm up to my 3rd PAP)... but hopefully I can look at your data later today. Stan

[Norie]

Zack

I've had another look at the DB and tried creating queries to just return the StDev and average.

The average is no problem but the StDev returns blank for all the fields I've tried so far.

[Zack Barresse]

Here is what I've done so far:

I created a seperate query which houses fields for Avg of all fields and StDev * 3 for all fields. I then joined that query with my original query and ended up with an SQL of the following ...


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;

I'm checking now to see if this works, as it does give me (what looks like) good results. Will let you know.

[Zack Barresse]

Just wanted to let everyone know that this SQL does in fact work as intended. Thanks everyone for helping! :D