Calculating field from previous record

[Shoutaro]

Hi
I have a problem (which in excel can be done very easily) in a query. I have a query which selects the vacation leave hours taken by each employee. I need to add a calculated field to be able to subtract the hours taken on the date from the amount available for that year.
e.g.
date VL hours Available
200
03/01/2018 8 192
25/01/2018 4 188
12/02/2018 3.5 184.5
06/03/2018 8 176.5

hoping that the colors are displayed well in the above example; the amount available is calculate in another query, while the date and hours taken are inputted, the data in green are the calculations i need! this is if 200 hr are available and 8 hrs are taken then 192hrs remain. on the next entry if 4hrs are taken then 188 hrs remain etc..

I believe this in access can be achieved in vba which is not my forte
if anyone can guide me or help me in this task I would really appreciated.

thanks

 

[Uncle Gizmo]

Bit of background on "Time" which you might find useful... MS Access - Add Time to a Date - Nifty Access

 

[plog]

What you want is essentially a 'running sum'. Search the forum for that term and you will find this has been multiple times.

In general, the simplest way to achieve that is with a DSum() to total all the data in the 'prior' records. Additionally, and for extra credit it can be done with a correlated sub-query.

Again, search the forum for 'running sum' you will find tons of examples.

 

[Shoutaro]

hi

thanks for your reply, but that is not what i really need, my fault because i wasn't clear enough. let me give it another try.

I need to loop through all records (like do while loop) and for each record work out the remaining hours. but i'm not that good in vba to do it without any guidance. the attached screenshot is from an excel that does exactly what I need.I hope that I more clear this time, my apologies.

thanks

 

[plog]

Nope, explained and understood correctly the first time.

You essentially need a running sum query. No VBA necessary, DSUM or correlated sub-query.

 

[arnelgp]

indeed a running sum.
note on this query:

Replace yourTable with the name of the table
that has date, hours, etc.
Replace [Available] with the Fieldname coming from the
other Query.
"OtherQuery" is the name of the other query where
the Initial Avalaible Hours is derived:

SELECT yourTable.Date, yourTable.Hours, ((SELECT [Available] FROM OtherQuery)-(SELECT SUM(HOURS) FROM yourTable AS T1 WHERE T1.[DATE] <= yourTable.[DATE])) AS Available
FROM yourTable;

 

[Shoutaro]

Hi thanks to all for your help it WORKED!! and most important I learnt something new for me that is running sum which i never used before.

I tried it at home cos I left work but I'll apply your advice as soon as I'll go back to work.

thanks again for your help

 

[Pat Hartman]

You should also understand the reason this is calculated on the fly in a query rather than being permanently stored in the table. Think of the problem that would occur if someone entered the leave time out of order or incorrectly.

 

[Shoutaro]

Dear arnelgp
Thanks for your help as I told you at home I tried it out and worked perfectly, but a work on my database which is more complex, since the data is obtained from a query that works out vacation from a bunch of different tables (this is due I need to work out events such as “Public Holidays”, “Braek time”, “OverTime” etc. etc.. however to make it work without complicating my life any further I took a sample data for a single employee in a new table and after a few tweaks, bang it worked!
The problem I’m facing now when I replicated the procedure in the query I need is a syntax error which I cannot spot. The line in the query design view that is giving me the error is:
Balance: ((SELECT [Available 2017] FROM qryStaffVLBF2017 WHERE StaffID=qryStaffVL 2017.[StaffID])-(SELECT SUM(Hours) FROM qryStaffVL AS T1 WHERE T1.[EventDate] <= qryStaffVL 2017.[EventDate]))
Can someone please help find what I’m doing wrong
Thanks

 

[isladogs]

Enclose the query name qryStaffVL 2017 in [] brackets as it contains a space.

Better still remove the space in the query name

 

[Pat Hartman]

shoutaro,
Think some more about what you are doing. Do you really want to recreate these queries every year? When you include data in the name of an object that is the implication. Perhaps you should start a new thread where we can help you to use arguments so the same query will work year after year and you won't have to keep rebuilding it.