Allocate Payments against each Invoice

[Sarma]

I have an Access query that shows that the following invoices are to be paid off:

Number / Amount

1 / 250

2 / 100

3 / 400

4 / 200

5 / 325

In another query, I have data of Amount received as 1,000.

Now I want this amount to be assigned against each of the invoices such that the oldest invoice is paid off first from the Amount available, as under:

Number / Amount / Paid Off

1 / 250 / 250

2 / 100 / 100

3 / 400 / 400

4 / 200 / 200

5 / 325 /

5th Invoice has not been paid off. Either it can show that only 50 has been paid against 325 or it can show blank.

Need help from experts.

Thanks.

 

[MarkK]

What if payment is not exact? What if two invoices are paid at once?

 

[plog]

You essentially want a modified Running Total Query. Paste this SQL and save it with the name 'RunningTotal"

SELECT YourTableNameHere.Number, YourTableNameHere.Amount, DSum("[Amount]","YourTableNameHere","Number<=" & [Number]) AS RunningTot
FROM YourTableNameHere;

Be sure to replace all instances of 'YourTableNameHere' with your table's/query's name.

That will get you a running total. Now to modify it to your needs, you will need to create a new query based on that query. This SQL will do what you want, based on a constant Amount Received of 1000:

SELECT RunningTotal.Number, RunningTotal.Amount, IIf(1000-[RunningTot]>=0,[Amount],"") AS PaidOff
FROM RunningTotal;

Since your Amount Received is coming from another query, you will have to modify the above code to use that instead of the constant 1000 that I have used.

 

[Sarma]

Plog: I am very grateful to you for the answer. I will try it out tomorrow as it is 01.13 am here in Dubai. Rgds.

 

[Sarma]

Plog:

I used the following code & did not get it OK.

SELECT [Table2].Number, [Table2].Invoice_Value, DSum("[Invoice_Value]","[Table2]","Number<=" & [Number]) AS RunningTot
FROM [Table2];

What is this 'Number' you have written ?

 

[plog]

Its the name of the field you gave me. See your first post.