Multi-Query Error?

[BradBrad]

Hi All,

Here is my code:

 SELECT [Influent Data - Monthly Summary Sheet].[SumOfTotal Flow (m3/day)]/1000 AS [Total Flow], ([Influent Data - Monthly Summary Sheet].[SumOfTotal Flow (m3/day)]/1000)/[Influent Data - Monthly Summary Sheet].[SumOfNumber of Samples] AS [Average Total Flow], Max([Influent Data].[Max Flow (m3/day)])/1000 AS [MaxOfMax Flow (m3/day)], Avg([BOD5]) AS Expr1, Avg([Influent Data].[Susp Solids (mg/L)]) AS [AvgOfSusp Solids (mg/L)], Avg([Influent Data].[Ammonia (mg/L)]) AS [AvgOfAmmonia (mg/L)], Avg([Influent Data].[Phosphorus (mg/L)]) AS [AvgOfPhosphorus (mg/L)], Sum([Final Effluent Data].[Chlorine Used]) AS [SumOfChlorine Used], Avg([Final Effluent Data].[Chlorine Dosage]) AS [AvgOfChlorine Dosage], Avg([Final Effluent Data].[Chlorine Residual]) AS [AvgOfChlorine Residual], Avg([Final Effluent Data].BOD) AS AvgOfBOD, Avg([Final Effluent Data].[Susp Solids (mg/L)]) AS [AvgOfSusp Solids (mg/L)1], Avg([Final Effluent Data].[Ammonia (mg/L)]) AS [AvgOfAmmonia (mg/L)1], Avg([Final Effluent Data].TKN) AS AvgOfTKN, Avg([Final Effluent Data].[Phosphorus (mg/L)]) AS [AvgOfPhosphorus (mg/L)1]
FROM [Return Sludge Data], [Influent Data], [Final Effluent Data], [Influent Data - Monthly Summary Sheet]
HAVING Format([Influent Data].[The Date],"mm/yyyy") = [Choose a Month - Format: mm/yyyy] OR Format([Final Effluent Data].[The Date],"mm/yyyy") = [Influent Data].[The Date];

and this chunk:

 [Influent Data - Monthly Summary Sheet].[SumOfTotal Flow (m3/day)]/1000

is producing an error saying its not part of a function..

What do I do to that to make it alright?

 

[boblarson]

Did you write this manually or use the QBE grid?

 

[BradBrad]

QBE grid to start, then manual edits.

Here's the updated code:

 SELECT [Influent Data - Monthly Summary Sheet].[SumOfTotal Flow (m3/day)]/1000 AS [Total Flow], [R1 Data By Month].[Total Flow]/[Influent Data - Monthly Summary Sheet].[SumOfNumber of Samples] AS [Average Total Flow], Max([Influent Data].[Max Flow (m3/day)]/1000) AS [MaxOfMax Flow (m3/day)], Avg([Influent Data].[BOD5]) AS Expr1, Avg([Influent Data].[Susp Solids (mg/L)]) AS [AvgOfSusp Solids (mg/L)], Avg([Influent Data].[Ammonia (mg/L)]) AS [AvgOfAmmonia (mg/L)], Avg([Influent Data].[Phosphorus (mg/L)]) AS [AvgOfPhosphorus (mg/L)], Sum([Final Effluent Data].[Chlorine Used]) AS [SumOfChlorine Used], Avg([Final Effluent Data].[Chlorine Dosage]) AS [AvgOfChlorine Dosage], Avg([Final Effluent Data].[Chlorine Residual]) AS [AvgOfChlorine Residual], Avg([Final Effluent Data].BOD) AS AvgOfBOD, Avg([Final Effluent Data].[Susp Solids (mg/L)]) AS [AvgOfSusp Solids (mg/L)1], Avg([Final Effluent Data].[Ammonia (mg/L)]) AS [AvgOfAmmonia (mg/L)1], Avg([Final Effluent Data].TKN) AS AvgOfTKN, Avg([Final Effluent Data].[Phosphorus (mg/L)]) AS [AvgOfPhosphorus (mg/L)1]
FROM [Return Sludge Data], [Influent Data], [Final Effluent Data], [Influent Data - Monthly Summary Sheet]
GROUP BY [Influent Data - Monthly Summary Sheet].[SumOfTotal Flow (m3/day)]/1000, [R1 Data By Month].[Total Flow]/[Influent Data - Monthly Summary Sheet].[SumOfNumber of Samples], Format([Influent Data].[The Date],"mm/yyyy"), Format([Final Effluent Data].[The Date],"mm/yyyy")
HAVING (((Format([Influent Data].[The Date],"mm/yyyy"))=[Choose a Month - Format: mm/yyyy])) OR (((Format([Final Effluent Data].[The Date],"mm/yyyy"))=[R1 Data by Month].[The Date]));

Now the dates are messing up.. I want the queries to use the same date but only have one prompt box. Any ideas?

 

[boblarson]

So is [The Date] a date field? Also it looks like you have 4 tables (or queries being referenced) but you haven't joined them. So, what's up with that? Also are there prompts in the other queries?

 

[BradBrad]

Correct, [The Date] is a field in each table that is auto-recorded when a new data entry is made. It is the Date/Time property and is in the form dd/mm/yyyy.

Return Sludge shouldn't be in there and has been removed.

There are prompts in the other queries, asking for the same time range (a month).

Here is the updated code:

 SELECT [Influent Data - Monthly Summary Sheet].[SumOfTotal Flow (m3/day)]/1000 AS [Total Flow], ([Influent Data - Monthly Summary Sheet].[SumOfTotal Flow (m3/day)]/1000)/[Influent Data - Monthly Summary Sheet].[SumOfNumber of Samples] AS [Average Total Flow], Max([Influent Data].[Max Flow (m3/day)])/1000 AS [MaxOfMax Flow (m3/day)], Avg([Influent Data].[BOD5]) AS Expr1, Avg([Influent Data].[Susp Solids (mg/L)]) AS [AvgOfSusp Solids (mg/L)], Avg([Influent Data].[Ammonia (mg/L)]) AS [AvgOfAmmonia (mg/L)], Avg([Influent Data].[Phosphorus (mg/L)]) AS [AvgOfPhosphorus (mg/L)], Sum([Final Effluent Data].[Chlorine Used]) AS [SumOfChlorine Used], Avg([Final Effluent Data].[Chlorine Dosage]) AS [AvgOfChlorine Dosage], Avg([Final Effluent Data].[Chlorine Residual]) AS [AvgOfChlorine Residual], Avg([Final Effluent Data].BOD) AS AvgOfBOD, Avg([Final Effluent Data].[Susp Solids (mg/L)]) AS [AvgOfSusp Solids (mg/L)1], Avg([Final Effluent Data].[Ammonia (mg/L)]) AS [AvgOfAmmonia (mg/L)1], Avg([Final Effluent Data].TKN) AS AvgOfTKN, Avg([Final Effluent Data].[Phosphorus (mg/L)]) AS [AvgOfPhosphorus (mg/L)1]
FROM [Influent Data], [Final Effluent Data], [Influent Data - Monthly Summary Sheet]
HAVING (((Format([Influent Data].[The Date],"mm/yyyy"))=[Choose a Month - Format: mm/yyyy])) OR (((Format([Final Effluent Data].[The Date],"mm/yyyy"))=[Influent Data].[The Date]));

 

[boblarson]

If you are going to limit it to date here, get rid of the other date prompts. But instead, use a form for date input. That is MUCH better (and more professional looking) for inputs. I consider parameter prompts a lazy way to work. But more importantly, with a form input you only have to enter once and the queries can all reference the form and then also you can supply the format to the queries better as using Format([Parameter Prompt Here], "mm/yyyy") doesn't really help. And it isn't going to work anyway. Because your date is a full date/time then you need to create a field with date only (to filter on):

MonthYear:Format([DateFieldHere], "mm/yyyy")

and then you can apply criteria to it.


Oh, and you might want to read this:

Reasons to Avoid Parameter Prompts

 

[BradBrad]

Bob - The Date/Time attribute is applied, but only the date is recorded (no time).

So you're saying there is no way to salvage anything I've done with the above code?

 

[BradBrad]

Got it working:

 SELECT [Influent Data - Monthly Summary Sheet].[SumOfTotal Flow (m3/day)]/1000 AS Expr2, ([Influent Data - Monthly Summary Sheet].[SumOfTotal Flow (m3/day)]/1000)/[Influent Data - Monthly Summary Sheet].[SumOfNumber of Samples] AS [Average Total Flow], Max([Influent Data].[Max Flow (m3/day)])/1000 AS [MaxOfMax Flow (m3/day)], Avg([Influent Data].BOD5) AS Expr1, Avg([Influent Data].[Susp Solids (mg/L)]) AS [AvgOfSusp Solids (mg/L)], Avg([Influent Data].[Ammonia (mg/L)]) AS [AvgOfAmmonia (mg/L)], Avg([Influent Data].[Phosphorus (mg/L)]) AS [AvgOfPhosphorus (mg/L)], Sum([Final Effluent Data].[Chlorine Used]) AS [SumOfChlorine Used], Avg([Final Effluent Data].[Chlorine Dosage]) AS [AvgOfChlorine Dosage], Avg([Final Effluent Data].[Chlorine Residual]) AS [AvgOfChlorine Residual], Avg([Final Effluent Data].BOD) AS AvgOfBOD, Avg([Final Effluent Data].[Susp Solids (mg/L)]) AS [AvgOfSusp Solids (mg/L)1], Avg([Final Effluent Data].[Ammonia (mg/L)]) AS [AvgOfAmmonia (mg/L)1], Avg([Final Effluent Data].TKN) AS AvgOfTKN, Avg([Final Effluent Data].[Phosphorus (mg/L)]) AS [AvgOfPhosphorus (mg/L)1]
FROM [Influent Data], [Final Effluent Data], [Influent Data - Monthly Summary Sheet]
WHERE (((Format([Influent Data].[The Date],"mm/yyyy"))=[Choose a Month - Format: mm/yyyy])) OR (((Format([Final Effluent Data].[The Date],"mm/yyyy"))=[Influent Data].[The Date]))
GROUP BY [Influent Data - Monthly Summary Sheet].[SumOfTotal Flow (m3/day)]/1000, ([Influent Data - Monthly Summary Sheet].[SumOfTotal Flow (m3/day)]/1000)/[Influent Data - Monthly Summary Sheet].[SumOfNumber of Samples];

Thanks for your help Bob, really appreciate you taking time out of your day to help me out. Will keep your resource in mind for future prompts.