ACCESS FEED: [MS_AccessPros] Getting a sum in a query

Rabu, 29 Juni 2011

[MS_AccessPros] Getting a sum in a query

Hello all,

I have 2 tables named
tblTasks
tblTaskDetails

I enter the amount of time on the form of the length of time for tasks.
the tblTasks is the main source for the form.
The tblTaskDetails is the source for the subform

I am trying to get a sum of the time spent for tasks or projects across both tables for a TaskID. Below is my sql statement. When I calculate the sums manually some work correctly but some are way off. Can anyone see what I am doing wrong. the query is the source for a form.

Thank you
Jim Wagner

SELECT tblTasks.TaskName, tblTasks.TaskTitle, tblTasks.TaskDescription, tblTasks.TaskNotes, tblTasks.StatusType, tblTasks.TaskDate, tblTasks.TaskID, tblTasks.BugNumber, tblTasks.Rank, DateDiff('d',[tblTasks].[TaskDate],Now()) AS [Length Of Project], tblTasks.TimeSpent, Sum([tblTasks]![TimeSpent]+[tblTaskDetails]![TimeSpent]) AS [Total Time Spent]
FROM tblTaskDetails RIGHT JOIN tblTasks ON tblTaskDetails.TaskId = tblTasks.TaskID
GROUP BY tblTasks.TaskName, tblTasks.TaskTitle, tblTasks.TaskDescription, tblTasks.TaskNotes, tblTasks.StatusType, tblTasks.TaskDate, tblTasks.TaskID, tblTasks.BugNumber, tblTasks.Rank, tblTasks.TimeSpent
HAVING (((tblTasks.TaskName) Not Like "*Training*") AND ((tblTasks.StatusType) Like "In Process"))
ORDER BY tblTasks.BugNumber DESC;