Solved Calculate previous record in current Record (1 Viewer)

[daud]

I am using a Db for calcualting pension. Pension is admissible on gross pension and then increases are added in gross pension. Every increase (percentage) is added in previous record and shown in current record. I am attaching the db as well illustrated the same as under:

1. Gross Pension : 660

Increase in pension 2017	10%	726	10% of 660 and plus 660
Increase in Pension 2018	15%	835	15% of 726 plus 726
Increase in Pension 2019	10%	918	10% of 835 plus 835

 

[XPS35]

FYI crossposted here https://www.accessforums.net/showthread.php?t=86667

 

[Pat Hartman]

What is the question? Are you trying to figure out what the previous pension amount is? Your tables don't seem to contain the information you would need. Also, we don't know any of your business rules so you might want to start with those rather than making us try to guess. For example, Are the increases ALWAYS on an annual basis or could they happen mid year? Even if the answer is always annual, you should probably consider using a start date even if it is always Jan 01, someyear. That way if there is a cost of living increase midyear, you can accommodate the change by using May 01, someyear for example.

 

[arnelgp]

here try your report.

 

[daud]

here try your report.

Great. May I have your whatsapp no. In fact i want some more guidace please

 

[daud]

What is the question? Are you trying to figure out what the previous pension amount is? Your tables don't seem to contain the information you would need. Also, we don't know any of your business rules so you might want to start with those rather than making us try to guess. For example, Are the increases ALWAYS on an annual basis or could they happen mid year? Even if the answer is always annual, you should probably consider using a start date even if it is always Jan 01, someyear. That way if there is a cost of living increase midyear, you can accommodate the change by using May 01, someyear for example.

1. No criteria for grant of pension. Its granted manually once government announces.
2. We have one figure thats say 660.
3. Once an increase is granted say its 10%, then calculation = 660*10%+660 against that record where 10% is entered.
4. Generally previous record is calculated with the given value and added in new value that came after calcuation with % value

 

[Pat Hartman]

Great. May I have your whatsapp no. In fact i want some more guidace please

You've been a member for a long time so you should know better than to ask for FREE private consulting services. The experts donate their time here in an effort to support Access and help the community. If you want private consulting services, you should expect to pay market rate for them. Either stick to the forum where everyone can benefit from your discussions or PM @arnelgp and offer to pay him.

 

[daud]

here try your report.

Third record's result is wrong. It should be multiplied as [New Pension] in previous record * 15% and add [New Pension] of previous record. Here I attached result with manual calculation in 2nd last column.

DeptNo​	CNIC​	Relationship​	Percentage​	Pension​	new_pension​	Correct Value​	Reason​
1712​	81202​	wife	10​	660​	726​	Correct​	​
1712​	81202​	wife	10​	660​	798.6​	Correct​	​
1712​	81202​	wife	15​	660​	878.46​	918.39​	798.6*15%+798​
1712​	81202​	wife	10​	660​	1010.229​	Incorrect​	​
1712​	81202​	wife	15​	660​	1111.2519​	Incorrect​	​

 

[arnelgp]

i modified the function.
also i add Sort Order on the query:

ORDER BY sub_Tbl1.CNIC,sub_Tbl1.Type_Pension;

check the query now

 

[Gasman]

Third record's result is wrong. It should be multiplied as [New Pension] in previous record * 15% and add [New Pension] of previous record. Here I attached result with manual calculation in 2nd last column.

DeptNo​	CNIC​	Relationship​	Percentage​	Pension​	new_pension​	Correct Value​	Reason​
1712​	81202​	wife	10​	660​	726​	Correct​	​
1712​	81202​	wife	10​	660​	798.6​	Correct​	​
1712​	81202​	wife	15​	660​	878.46​	918.39​	798.6*15%+798​
1712​	81202​	wife	10​	660​	1010.229​	Incorrect​	​
1712​	81202​	wife	15​	660​	1111.2519​	Incorrect​	​

That is gratitude for you