I just want totals in my Query

  • Thread starter Thread starter bugman
  • Start date Start date
B

bugman

Hi,
I'm trying to create a query that will return the total of all
hours spent on a task. The table has about 4500 and I want just the
total hours worked by task number (dpr in this case). The select
criteria has some logic that filters by certain accounts and if the task
description has a 3 or 4 digit numeric code. Every thing works
correctly but for those records that have hours worked in 2 accounts the
query returns a line with the total for each account even though the
task number is the same. I want just the total hours by task. The SQL
looks like this: Suggestions?

SELECT Sum(TL_RPTD_DETAILS.Hours) AS [Sum Of HXActhrs], Right
([ActivityID],4) AS [DPR#]
FROM TL_RPTD_DETAILS
WHERE (((TL_RPTD_DETAILS.[Proj/Grt]) Like 'NSLIJ8100[2-4]' Or
(TL_RPTD_DETAILS.[Proj/Grt]) Like 'NSLIJ7100[2-4]' Or (TL_RPTD_DETAILS.
[Proj/Grt])='NSLIJ90000'))
GROUP BY Right([ActivityID],4)
HAVING (((Right([ActivityID],4)) Like "*#" Or (Right([ActivityID],4))
='PMO'))
ORDER BY Right([ActivityID],4);
 
You culd try duplicating the activity ID and putting the
criteria on the second copy with Where instead of using
the Having. Using Having means it runs the whole query,
then restricts it. Where will restrict first then run
the query.

If that does not work, then create a query that pulls out
the task code, then base the total query on that.

Also since you are grouping by the activity ID, the
result will be sorted by that also. So the Order by is
redundant. j.
 
Back
Top