If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Running Total / Concatinate question
I'm trying to get a running total for hours worked that are divided by
project/by date. I have the following fields: Project ID number (saved as text) Date Worked Project Name Hours Worked Concan (concatination of Project ID and Date worked so as to create a unique number that specifies the specific project on a specific date, ie - 20060011/1/2005 I'm using the following formula with "Test" as the query: RunningSum: (SELECT Sum([Hours Worked]) FROM [Test] AS [Test_1] WHERE [Test_1].[Concan] = [Test].[Concan]) The problem is that the returned results are not limiting themselves to the by project/by date sumation. The running total continues through the whole list. I need it to start over at 0 when it gets to a new project/date. Any help would be great! |
#2
|
|||
|
|||
Running Total / Concatinate question
Correction - the ID number is saved as a number...not as text.
|
#3
|
|||
|
|||
Running Total / Concatinate question
Post the SQL of your query and sample data.
"Daesthai" wrote: I'm trying to get a running total for hours worked that are divided by project/by date. I have the following fields: Project ID number (saved as text) Date Worked Project Name Hours Worked Concan (concatination of Project ID and Date worked so as to create a unique number that specifies the specific project on a specific date, ie - 20060011/1/2005 I'm using the following formula with "Test" as the query: RunningSum: (SELECT Sum([Hours Worked]) FROM [Test] AS [Test_1] WHERE [Test_1].[Concan] = [Test].[Concan]) The problem is that the returned results are not limiting themselves to the by project/by date sumation. The running total continues through the whole list. I need it to start over at 0 when it gets to a new project/date. Any help would be great! |
#4
|
|||
|
|||
Running Total / Concatinate question
Try the following. It should give a running Sum for each by Project by
Date Worked. RunningSum: (SELECT Sum([Hours Worked]) FROM Test as Test1 WHERE Test1.Project = Test.Project AND Test1.[Date Worked] = Test.[Date Worked]) '================================================= === John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === Daesthai wrote: I'm trying to get a running total for hours worked that are divided by project/by date. I have the following fields: Project ID number (saved as text) Date Worked Project Name Hours Worked Concan (concatination of Project ID and Date worked so as to create a unique number that specifies the specific project on a specific date, ie - 20060011/1/2005 I'm using the following formula with "Test" as the query: RunningSum: (SELECT Sum([Hours Worked]) FROM [Test] AS [Test_1] WHERE [Test_1].[Concan] = [Test].[Concan]) The problem is that the returned results are not limiting themselves to the by project/by date sumation. The running total continues through the whole list. I need it to start over at 0 when it gets to a new project/date. Any help would be great! |
Thread Tools | |
Display Modes | |
|
|