Jim-
Ah, that explains it.
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Jim Wagner
Sent: Thursday, June 30, 2011 3:30 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Getting a sum in a query
John,
I have a one to many relation going on between the two tables. I set up the
original task and enter the amount of time I took. If the task takes longer than
a day I have a subform that I can enter subsequent follow up tasks related to
the original task, which I enter the time it took for the sub task. I probably
should have given a different name for the Time spent in the tblTaskDetails
table.
Jim Wagner
________________________________
________________________________
From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Wed, June 29, 2011 9:47:49 PM
Subject: RE: [MS_AccessPros] Getting a sum in a query
Jim-
You should have ended up with something like this:
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,
([tblTasks]![TimeSpent] + Sum([tblTaskDetails]![TimeSpent])) AS [Total Time
Spent]
FROM tblTaskDetails RIGHT JOIN tblTasks ON tblTaskDetails.TaskId =
tblTasks.TaskID
WHERE (((tblTasks.TaskName) Not Like "*Training*") AND ((tblTasks.StatusType)
Like "In Process"))
GROUP BY tblTasks.TaskName, tblTasks.TaskTitle, tblTasks.TaskDescription,
tblTasks.TaskNotes, tblTasks.StatusType, tblTasks.TaskDate, tblTasks.TaskID,
tblTasks.BugNumber, tblTasks.Rank, tblTasks.TimeSpent
ORDER BY tblTasks.BugNumber DESC;
What you were doing before was adding in tblTasks.TimeSpent once for each
matching row in tblTaskDetails.
What puzzles me is why you have two TimeSpent fields. Shouldn't the total for
the task equal just the total of all the task details? Why is there a TimeSpent
field in tblTasks?
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Jim Wagner
Sent: Thursday, June 30, 2011 5:48 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Getting a sum in a query
Duane,
I never received the response. That is why I reposted it. I was able to get it
to work by switching the time spent so the tblTasks were summing the sum of the
tblTaskDetails. Never thought to think logically.
Jim Wagner
________________________________
________________________________
From: Duane Hookom <duanehookom@hotmail.com>
To: Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com>
Sent: Wed, June 29, 2011 8:16:35 PM
Subject: 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
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar