RE: [MS_AccessPros] Getting a sum in a query

Jim,
I thought I had replied to this message earlier. Was it possibly in a different group?

You are going to total the [tblTasks]![TimeSpent] for each related record in tblTaskDetails. You could use a union query to get the correct sum by tblTasks.TaskName. Another option is to sum the TimeSpent in tblTaskDetails by TaskId and then join this aggregate value in a query to tblTasks.

You could have troubleshot your query by removing the group by and pasting the results into Excel. You would easily identify the TimeSpent from tblTasks would be listed multiple times where there were multiple related records int tblTaskDetails.

Duane Hookom
MS Access MVP

To: MS_Access_Professionals@yahoogroups.com
From: luvmymelody@yahoo.com
Date: Wed, 29 Jun 2011 21:31:28 +0000
Subject: [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;

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/