MS Access undo running sum

I have a query that filters one month of daily data from a table. Fields include DAY: 1,2...31 and cumulative flow FLOW: 1000, 2500, 3000 etc

I want to be able to get the daily flow. i.e. day 1 is (2500-1000) = 1500; day 2 is (3000-2500) = 500 etc.

I believe someone on here did this many years ago but I cant find it.
LVL 2
hgj1357Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fabrice LambertFabrice LambertCommented:
Maybe with a query:
SELECT day, flow, T2.flow - T1.flow AS dailyFlow
FROM Table1 AS T1 INNER JOIN Table1 AS T2
ON T1.day = T2.day-1

Open in new window

0
Ryan ChongCommented:
Fabrice's suggestion will omit the last record from your Table, and if that's crucial, then you may try this instead.

SELECT T1.day, T1.flow, (select T2.flow from Table1 As T2 where T2.day-1 = T1.day) - T1.flow As dailyFlow
FROM Table1 AS T1

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hgj1357Author Commented:
yeah i figured the last day or first day would be a problem.  I ended up using a delete quesry / append query to write to two tables, link them and subtract subsequent days' flow.  Not as elegant as yours! But it works.

Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.