sum from initial values minus values used in other table (1 Viewer)

Hi all,
I have a table where I have all the rewards people got. We have an initial budget for this for example like 100 dollars. 3 people got 10 dollars each. This means I should have 70 dollars left.
Can I do this in a query and feed the initial value from a field in the main form?
I have the table: tbl_people_reward, the field nane is simply: amount
And I have a field called: initial_amount in the tbl_main_data

Any idea if this is possible?

Greetings.

for query you use:

select initial_amount, (select sum(amount) from tbl_people_award) as award, initial_amount-(select sum(amount) from tbl_people_award) as balance from tbl_main_data

for control source of textbox:

=dlookup("initial_amount", "tbl_main_data") -dsum("amount", "tbl_people_award")

you would use append query to write the reward to the person's income table
tPersonPayments table
personID, Amt, PayDte, Note, reconcile
123, $10, 1/1/17, 'reward', false

you would then have the reward bucket filled w $100, : tReward.Budget
then reduce it for each record and set reconcile to TRUE.

I dont think it can be done with just queries. Youd want to use code to loop thru the tPersonPayments table and verify the tReward.Budget field still has money in it and stop when it runs out.

loop thru tPersonPayments table:
vPerson = PersonID
vDate = [PayDte]

if tReward.Budget-Amt>=0 then tReward.Budget= tReward.Budget-Amt
'then update the tPersonPayments table

ssql = "update tPersonPayments set [reconcile]=true where personid =" & vPerson & " and [payDt] = #" & vDate & "#"

endif

Hi Arnelgp,

It works really nice

Ranman256, I will look into your solution too, seems also nice