aggregate function does not include an expression?

[phil access]

Field 1 is Date, Field 2 is time, Field 3 is elapsed time, Field 4 is
temperature, Field 5 is pressure head,


Error message is:
You tried to execute a query that does not include the specified expression
'4.28*0.577*0.5773*([Field4])+0.004^2.5'
as part of an aggregate function.

SELECT SN18941_apr1_noheadersa.Field1, Sum(SN18941_apr1_noheadersa.Field4)
AS SumOfField4, (4.28*0.577*0.5773*([Field4])+0.004^2.5) AS cfs, [gpd] AS
Expr2, Sum([gpm]*6*60*24)/(1000000) AS mgd, [cfs]/0.00222800925926666 AS gpm
FROM SN18941_apr1_noheadersa
WHERE (((Month([field1]))=[what month?]))
GROUP BY SN18941_apr1_noheadersa.Field1, [gpd];


What is the error in this query?

 

[ChrisJ]

try...
SELECT
SN18941_apr1_noheadersa.Field1,
Sum(SN18941_apr1_noheadersa.Field4) AS SumOfField4,
(4.28*0.577*0.5773*([Field4])+0.004^2.5) AS cfs,
[gpd] AS Expr2,
Sum([gpm]*6*60*24)/(1000000) AS mgd,
[cfs]/0.00222800925926666 AS gpm
FROM
SN18941_apr1_noheadersa
WHERE
Month([field1])=[what month?]
GROUP BY
SN18941_apr1_noheadersa.Field1,
(4.28*0.577*0.5773*([Field4])+0.004^2.5),
[gpd],
[cfs]/0.00222800925926666;

 

[Chaim]

You have a Select with a GROUP BY clause that does not use that expression
either as part of the GROUP BY list or in an aggregate function. There is
nothing wrong with the expression per se.
There is no complaint about Field1 or gpd because they appear in the GROUP
BY list.
There is no complaint about Field4 or the expression aliased as mgd because
they are part of Sum() aggregates.
I assume there is no complaint about cfs because it complained about the
expression already.
Why do you have a date field and a separate time field? Why, when you can
calculate an elapsed time from data that you presumably are capturing, do
record an elapsed time? But these are just general ramblings about the
structure.

 

[MGFoster]

Field 1 is Date, Field 2 is time, Field 3 is elapsed time, Field 4 is
temperature, Field 5 is pressure head,


Error message is:
You tried to execute a query that does not include the specified expression
'4.28*0.577*0.5773*([Field4])+0.004^2.5'
as part of an aggregate function.

SELECT SN18941_apr1_noheadersa.Field1, Sum(SN18941_apr1_noheadersa.Field4)
AS SumOfField4, (4.28*0.577*0.5773*([Field4])+0.004^2.5) AS cfs, [gpd] AS
Expr2, Sum([gpm]*6*60*24)/(1000000) AS mgd, [cfs]/0.00222800925926666 AS gpm
FROM SN18941_apr1_noheadersa
WHERE (((Month([field1]))=[what month?]))
GROUP BY SN18941_apr1_noheadersa.Field1, [gpd];


What is the error in this query?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

This is a summation query (has a GROUP BY clause), which means any
column/expression in the SELECT column-list that isn't an aggregate
function has to be in the GROUP BY clause. Your query changed:

SELECT SN18941_apr1_noheadersa.Field1,
Sum(SN18941_apr1_noheadersa.Field4)AS SumOfField4,
(4.28*0.577*0.5773*([Field4])+0.004^2.5) AS cfs, [gpd] AS
Expr2, Sum([gpm]*6*60*24)/(1000000) AS mgd, [cfs]/0.00222800925926666 AS
gpm
FROM SN18941_apr1_noheadersa
WHERE Month([field1])=[what month?]
GROUP BY SN18941_apr1_noheadersa.Field1,
(4.28*0.577*0.5773*([Field4])+0.004^2.5), [gpd],
[cfs]/0.00222800925926666

You may rearrange the order of the GROUP BY to get the results you want.

BTW, "[gpd] AS Expr2" means that the column designation [gpd] is not a
recognized column name in the table. If "gpd" is a valid column name,
you may want to remove "As Expr2" for the SQL string.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQtwR5YechKqOuFEgEQKX6QCdEAwK7dyHdR/+68ezlcTcpyvwvZ8AniKK
h4Myzm1cFUJppoM0T0GiMytl
=lnp9
-----END PGP SIGNATURE-----