Summing a field in a query

[Chris F via AccessMonster.com]

Hello

I have the below query based upon two tables. One table saves the design
length of a pipe for each pipe given a name (D1=100 feet, D2 = 200 feet, ect..
) , which is recorded only once. As a crew constructs the pipe, another
table will be updated with their progress each day. (D1=25 feet, D1=50 feet,
D2=100 feet) I would like to see in a datasheet, the designation of the pipe
and the Length left to construct, which would be the total length from the
design table subtracting the sum of the constructed lengths. D1=25 feet,
D2 = 100 feet

I thought I could accomplish this in one query, where I created two
expressions, one being a sum function (SumLength) of each days pipe
construction and a second expression (LengthLeft) which would subtract the
sumlength from the design length. However, when I run this query it gives me
an error saying about an aggregate function. I thought if I showed the Group
it would be ok, but still gives me an error.

SELECT t2PTaskTakeoff.Designation, Sum(t3SRActivityTakeoffWork.Length) AS
SumLength, ([t2PTaskTakeoff].[Length]-NZ([SumLength],0)) AS LengthLeft,
t2PTaskTakeoff.PTaskID, t2PTaskTakeoff.PTaskTakeoffID,
t3SRActivityTakeoffWork.SRTakeoffWorkID
FROM t2PTaskTakeoff LEFT JOIN t3SRActivityTakeoffWork ON t2PTaskTakeoff.
PTaskTakeoffID = t3SRActivityTakeoffWork.PTaskTakeoffID
GROUP BY t2PTaskTakeoff.Designation, ([t2PTaskTakeoff].[Length]-NZ([SumLength]
,0)), t2PTaskTakeoff.PTaskID, t2PTaskTakeoff.PTaskTakeoffID,
t3SRActivityTakeoffWork.SRTakeoffWorkID
HAVING (((t2PTaskTakeoff.PTaskID)=[TempVars]![CPTaskID]));

Any help would be greatly appreciated.
Thanks,
Chris F.

 

[KARL DEWEY]

Try this --
SELECT t2PTaskTakeoff.Designation, t2PTaskTakeoff.Length,
Sum(t3SRActivityTakeoffWork.Length) AS Work_Comp,
[t2PTaskTakeoff].[Length]-Sum([t3SRActivityTakeoffWork].[Length]) AS
Work_To_Comp
FROM t2PTaskTakeoff LEFT JOIN t3SRActivityTakeoffWork ON
t2PTaskTakeoff.Designation = t3SRActivityTakeoffWork.Designation
GROUP BY t2PTaskTakeoff.Designation, t2PTaskTakeoff.Length;