[AccessD] Sum a union query

Hi Chester

Seems like a Group By is missing.
And sorting isn't needed. 
Also note UNION ALL.
Try this:

<SQL>

  SELECT 
    ACTVDATE, 
    Sum(Exp1), 
    Sum(Exp2)
  FROM
    (SELECT 
      ACTVDATE, 
      Sum(VOLUME_SS) AS Exp1, 
      Sum([VOLUME_SS])*0.335849 AS Exp2
    FROM 
      T9CAT_GAS_MTR_READINGS
    WHERE 
      GASMTRIDNO IN (605954,605955,605956,425030) 
      AND
      ACTVDATE>=#10/1/2004#
    GROUP BY 
      ACTVDATE
    UNION ALL
    SELECT 
      ACTVDATE, 
      Sum([VOLUME_SS]*-1) AS Exp1, 
      Sum([VOLUME_SS])*-0.335849 AS Exp2
    FROM 
      T9CAT_GAS_MTR_READINGS
    WHERE 
      GASMTRIDNO=605957 
      AND  
      ACTVDATE>=#10/1/2004#
    GROUP BY
      ACTVDATE)
  GROUP BY 
    ACTVDATE
  ORDER BY 
    ACTVDATE;

</SQL>

/gustav

>>> kaupca at chevrontexaco.com 02-12-2004 23:33:55 >>>
Can I do this in a union query? Generates error of no aggregate
function
for ACTVDATE.

Select ACTVDATE, Sum(Exp1), Sum(Exp2)
From
(SELECT ACTVDATE, Sum(VOLUME_SS) as Exp1, Sum([VOLUME_SS])*0.335849 as
Exp2
FROM T9CAT_GAS_MTR_READINGS
WHERE GASMTRIDNO IN (605954,605955,605956,425030) and
ACTVDATE>=#10/1/2004#
GROUP BY ACTVDATE
ORDER BY ACTVDATE
UNION
SELECT ACTVDATE, Sum([VOLUME_SS]*-1)as Exp1,
Sum([VOLUME_SS])*-0.335849
as Exp2
FROM T9CAT_GAS_MTR_READINGS
WHERE GASMTRIDNO=605957 and  ACTVDATE>=#10/1/2004#)
GROUP BY ACTVDATE
ORDER BY ACTVDATE;

Chester Kaup
Information Management Technician
IT-MidContinent/MidContinent Business Unit
CTN 8-687-7415  
Outside 432-687-7415