Running Total / Concatinate question

[Guest]

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!

 

[Guest]

Correction - the ID number is saved as a number...not as text.

 

[Guest]

Post the SQL of your query and sample data.

 

[John Spencer]

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
'====================================================