Hello
It's my first post here. I'm not much of a Fox kind of guy, but somebody asked me to help him with a problem.
Don't know how to do it, so I'll pay it forward. If anyone could help me, that would be great. If not, well, that's life and thanks for trying.
There are 3 tables:
1. Debits - with 3 fields: month, year and debit (sum of money).
2. Receipts with 3 fields: receipt_no, date, value (sum of money).
3. Outputs with the following fields: month, year, debit, receipt_no, date, value
So basically the situation is this: in debits you record the utilities for an apartment which are monthly.
In receipts you have the receipts which are proof that the guy actually pays his utilities.
The outputs table should contain records of debits being paid.
But like this:
Example 1
John has a bill for his utility in 01 (January) 2015 of 60$.
John has a bill for his utility in 02 (February) 2015 of 20$.
John has a bill for his utility in 03 (March) 2015 of 20$.
John pays the utilities in April on 02.04.2015 and he receives a receipt with no 1 dated accordingly. And he only pays 90$, leaving 10$ debit.
I'd like the output table to have these recordings:
month 1
year 2015
debit 60$
receipt_no 1
date 02.04.2015
value 60$
month 2
year 2015
debit 20$ - cause he already paid for the 60$ in January in the first record
receipt_no 1
date 02.04.2015
value 20$
month 3
year 2015
debit 20$ - cause he already paid for January and February.
receipt_no 1
date 02.04.2015
value 10$
and an empty recording with just the field Value completed with the value of 10$ which were not paid.
So basically I want the debits to be paid in cronological order in conformity with the receipts (also in cronological order).
Thanks for reading so far. I hope my english doesn't suck very bad and that I've made myself understood.
Example 2:
John has a bill for his utility in 01 (January) 2015 of 10$.
John has a bill for his utility in 02 (February) 2015 of 10$.
John has a bill for his utility in 03 (March) 2015 of 10$.
John pays the utilities in April on 01.04.2015 and he receives a receipt with no 1 dated accordingly. He pays 15$.
John pays the utilities again in April on 02.04.2015 and he receives a receipt with no 2 dated accordingly. He pays another 14$.
Output table
month 1
year 2015
debit 10$
receipt_no 1
date 01.04.2015
value 10$
month 2
year 2015
debit 10$
receipt_no 1
date 01.04.2015
value 5$
month 2
year 2015
debit 5$
receipt_no 2
date 02.04.2015
value 5$
month 3
year 2015
debit 10$
receipt_no 2
date 02.04.2015
value 9$
and an empty recording with just the field Value completed with the value of 1$ which were not paid.
He should cover the debit of each month (in cronological order) using payments which he made (in cronological order - receiving receipts in return) and move to the pay the debit for the following month after he paid in full for the first one.
It's my first post here. I'm not much of a Fox kind of guy, but somebody asked me to help him with a problem.
Don't know how to do it, so I'll pay it forward. If anyone could help me, that would be great. If not, well, that's life and thanks for trying.
There are 3 tables:
1. Debits - with 3 fields: month, year and debit (sum of money).
2. Receipts with 3 fields: receipt_no, date, value (sum of money).
3. Outputs with the following fields: month, year, debit, receipt_no, date, value
So basically the situation is this: in debits you record the utilities for an apartment which are monthly.
In receipts you have the receipts which are proof that the guy actually pays his utilities.
The outputs table should contain records of debits being paid.
But like this:
Example 1
John has a bill for his utility in 01 (January) 2015 of 60$.
John has a bill for his utility in 02 (February) 2015 of 20$.
John has a bill for his utility in 03 (March) 2015 of 20$.
John pays the utilities in April on 02.04.2015 and he receives a receipt with no 1 dated accordingly. And he only pays 90$, leaving 10$ debit.
I'd like the output table to have these recordings:
month 1
year 2015
debit 60$
receipt_no 1
date 02.04.2015
value 60$
month 2
year 2015
debit 20$ - cause he already paid for the 60$ in January in the first record
receipt_no 1
date 02.04.2015
value 20$
month 3
year 2015
debit 20$ - cause he already paid for January and February.
receipt_no 1
date 02.04.2015
value 10$
and an empty recording with just the field Value completed with the value of 10$ which were not paid.
So basically I want the debits to be paid in cronological order in conformity with the receipts (also in cronological order).
Thanks for reading so far. I hope my english doesn't suck very bad and that I've made myself understood.
Example 2:
John has a bill for his utility in 01 (January) 2015 of 10$.
John has a bill for his utility in 02 (February) 2015 of 10$.
John has a bill for his utility in 03 (March) 2015 of 10$.
John pays the utilities in April on 01.04.2015 and he receives a receipt with no 1 dated accordingly. He pays 15$.
John pays the utilities again in April on 02.04.2015 and he receives a receipt with no 2 dated accordingly. He pays another 14$.
Output table
month 1
year 2015
debit 10$
receipt_no 1
date 01.04.2015
value 10$
month 2
year 2015
debit 10$
receipt_no 1
date 01.04.2015
value 5$
month 2
year 2015
debit 5$
receipt_no 2
date 02.04.2015
value 5$
month 3
year 2015
debit 10$
receipt_no 2
date 02.04.2015
value 9$
and an empty recording with just the field Value completed with the value of 1$ which were not paid.
He should cover the debit of each month (in cronological order) using payments which he made (in cronological order - receiving receipts in return) and move to the pay the debit for the following month after he paid in full for the first one.