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