RE: [MS_AccessPros] Getting a sum from 2 tables in a query

You are summing the task Time spent for each of the child related records. You might want to first create a totals query of only the task details and then join it to the task table.

To: MS_Access_Professionals@yahoogroups.com
From: luvmymelody@yahoo.com
Date: Tue, 28 Jun 2011 23:01:19 +0000
Subject: [MS_AccessPros] Getting a sum from 2 tables 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/