Calculated value of previous year carrying over in next year (iteration) (1 Viewer)

faucillon

New member
Local time
Today, 14:51
Joined
Aug 22, 2017
Messages
4
Dear SQL supermen (& -women),

Here is the challenge I'm experiencing at this moment.

I am setting up a Flexible Rewarding System Database in Access, which will hold all of the benefits chosen by our employees and the 'budget' allocated to those benefits. (E.g. Car for x euro / month, Fuel card with Y euro / year...)

To get an overview of the fuel cards we need to see the following:
-The budget allocated by the employee to the fuel card per year
-The total transactions made by the employee with the fuel card
-The total commute indemnity awarded to the employee on the fuel card
-The total of the abovementioned 3 elements on top of the remaining budget for the previous year.

All this is based on the following tables:
-A table which has 1 record for the fuel card per employee per year
-A table which has all the fuel transactions of the employee per that year
-A table which has all the commute indemnity transaction to per year

I also created the following Queries:
-A query that sums the transactions by 'group by' Year & employee => resulting in a total per employee per year
-A query that joins the various relevant fields (budget, total of transactions)
[note: commute indemnity is not included yet but will rely on an identical query as the fuel transactions thus I will exclude it from my question]

Thus I now have a query resulting in a table that gives me:
-employee number
-year
-Total of fuel transactions for that year

What I Still need is the 'remaining budget for each year, whilst keeping in mind the remaining budget of the previous year'

In other words:
Year 1 => remaining budget = sum of budget, indemnity total and transactions total
Year 2 => remaining budget = sum of budget, indemnity total and transactions total & Remaining budget of last year
Year 3 => remaining budget = sum of budget, indemnity total and transactions total & Remaining budget of last year (including the remaining budget of Year 1)
...

However as far as I understand SQL, it executes the command on all records simultaneously. My required result however requires the remaining budget to keep in mind the remaining budget of all previous years for that employee (leading me to think that this needs to be calculated iterative).
Is this possible through SQL/query or am I forced to run a macro for this?

An alternative would be a type of DLookup in a table (calculated field), but I'm not certain if that is even possible (I think it's not) as well as it doesn't seem desirable for a stable set-up.

Oh great Office Oracles, enlighten me with your wisdom as to how I should approach this issue please.

Due to the content of the tables I am unable to disclose the database but I can always set up a dummy database if need be.
I hope to have clarified the situation sufficiently as is, if not let me know!

Thank you kindly in advance!

Faucillon
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:51
Joined
May 7, 2009
Messages
19,169
You'll be needing a query with sub query to accomplished that.
 

plog

Banishment Pending
Local time
Today, 08:51
Joined
May 11, 2011
Messages
11,611
Please provide sample data to demonstrate your issue. I need 2 sets of data:

A. Starting sample data from your tables. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show me what data you expect to end up with when you feed in your data from A.
 

faucillon

New member
Local time
Today, 14:51
Joined
Aug 22, 2017
Messages
4
Wow! Quick replies! Thank you very much for that instant support.
As it happens (after painfully tearing through webpages for days without any succes) I believe to finally have discovered the solution (combination of query and macro) for this problem in the VBA-part of this forum which seems to run smoothly.

I'll give that a try and post if it is indeed operational. (In my case the macro would need to run over a much larger data set (several hundred and increasing, thus runtime could be an issue.)
If it does not resolve this issue I will definitely provide an example of the issue with sample data.

Thank you very much for the feedback!

Greetings,

Faucillon
 

faucillon

New member
Local time
Today, 14:51
Joined
Aug 22, 2017
Messages
4
Hello everybody,

I have been able to rework the set up presented in another post to suit my needs perfectly in this other thread i mentioned.

You can find it here:
Run the only query in the attached database.

(By the way, again thanks for the help!!)
 
Last edited:

Users who are viewing this thread

Top Bottom