Multi-Query Error?

[BradBrad]

Hi All,



I'm querying 2 tables and one query to create a "summary" page for a client.

I'm having some difficulties however, with the dates. I want to prompt the user to type in one month in the form mm/yyyy and then that will filter the results of the calculations below based on the month entered.

Any ideas?

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?

[BradBrad]

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]));

[rpeare]

Did you fix your problem or are you still having an issue? If you still have a problem could you post a sample db