Totalling all values in a field and getting one number in a query

[Hatchetman240]

SELECT Temp.[Tracking Number Ren], Temp.[Renewed Ticket Date], Temp.[Renewed Ticket Number], Temp.[Ren Exp Date], Temp.[Utility Locate Co], Temp.[Loc Renewal Date], Temp.FD, Temp.HD, Temp.H
FROM Temp
GROUP BY Temp.[Tracking Number Ren], Temp.[Renewed Ticket Date], Temp.[Renewed Ticket Number], Temp.[Ren Exp Date], Temp.[Utility Locate Co], Temp.[Loc Renewal Date], Temp.FD, Temp.HD, Temp.H
HAVING (((Temp.[Loc Renewal Date]) Between [Start Date ] And [End Date]));


What I am trying to do is get three totals like this:

FD HD H
1 16 8 3

With everything I have tried all I get is the list of every entry instead of just those totals. I was trying to just ignore it and total on my report, but then I realized I would need the above results for other things too.

Thanks.

 

[PaulBricker]

Try this

SELECT Temp.[Tracking Number Ren], Temp.[Renewed Ticket Date], Temp.[Renewed Ticket Number], Temp.[Ren Exp Date], Temp.[Utility Locate Co], Temp.[Loc Renewal Date], Sum(Temp.FD) as SumOfFD, Sum(Temp.HD) as SumOfHD, Sum(Temp.H) as SumOfH
FROM Temp
GROUP BY Temp.[Tracking Number Ren], Temp.[Renewed Ticket Date], Temp.[Renewed Ticket Number], Temp.[Ren Exp Date], Temp.[Utility Locate Co], Temp.[Loc Renewal Date]
HAVING (((Temp.[Loc Renewal Date]) Between [Start Date ] And [End Date]));


Paul

 

[Hatchetman240]

I had actually already tried the same thing, but it returns a set of records instead of just one row with the totals I need.

 

[PaulBricker]

Well, that is probably a result of the multiple GroupBy fields that you have. Maybe a little more information will help, but unless all the values for [Tracking Number Ren], [Renewed Ticket Date],.....[Loc Renewal Date]
are the same for every record returned, you are going to get multiple records with values for the three fields you are summing. I mean that's a lot of Fields to be Grouping On to return one record.

Paul