A Challenge Query

[aldunford]

I have a query that gives me the total Sum of Hours and Date By Month.

I need to Take The beginning Balance and subtract only the Sum of Hours for
Jan 08. How can I do that?

example


Sum of Hours Date By Month Beginning Bal Answer
3 Feb 08 250 262
25 Jan 08 250 240

I need the query to do that first. Then I want it to take Sum of Hours for
the remainder following month and subtract that from Answer. I think I will
need to do a subquery for the second part.

so the results would be

Sum of Hours Date By Month Beginning Bal Answer
3 Feb 08 250 222
25 Jan 08 250 225

Thanks!!

 

[Ken Snell \(MVP\)]

Without knowing how your data are being stored, I'm just guessing at a
suggested query design here, but this may get you started:

SELECT SumOfHours, DateByMonth, BeginningBalance,
(BeginningBalance -
(SELECT Sum(T.SumOfHours) FROM TableName AS T
WHERE T.DateByMonth <= TableName.DateByMonth))
AS Answer
FROM TableName;

 

[aldunford]

I ran a query to get the sum of hours here is the sql

SELECT DISTINCTROW tblVacation.[SS# ID], Format$([tblVacation].[Date],'mmmm
yyyy') AS [Date By Month], Sum(tblVacation.Hours) AS [Sum Of Hours]
FROM tblVacation
GROUP BY tblVacation.[SS# ID], Format$([tblVacation].[Date],'mmmm yyyy'),
Year([tblVacation].[Date])*12+DatePart('m',[tblVacation].[Date])-1;

The beginning Balance is stored in a table called tblVacationBeginningBalance

So I'm try to write a query that will take the Beginning Balance and
subtract Sum of Hours by DateByMonth.

Beginning Balance 200

If I run the query of Sum of Hours

Jan 2008 10
Feb 2008 5


So I need to take the Beginning Balance of 200 subtract January 10 hrs = 190
Then next record needs to look at answer from January the 190 and subtract 5
so you get 185 for answer

It's so much easier on paper!! I tried what you gave but it says it will
only generate one answer and one give it to me. =(

 

[aldunford]

Thanks I got it to work!!

Without knowing how your data are being stored, I'm just guessing at a
suggested query design here, but this may get you started:

SELECT SumOfHours, DateByMonth, BeginningBalance,
(BeginningBalance -
(SELECT Sum(T.SumOfHours) FROM TableName AS T
WHERE T.DateByMonth <= TableName.DateByMonth))
AS Answer
FROM TableName;