How to take a cumulative previous year record?

[haissk]

In MS Access, I have a table like below:
FY Percent
2015 4.5%
2016 5%
2017 5.5%
2018 6.5%
2019 5%


2020 5%

Now I want to add a calculated row and that row should be calculated as shown below:
FY Calculated
2015 P * 1 (Multiply the value by 1 for first year)
2016 P * 2015 Calculated value (the above value)
2017 P * 2016 Calculated Value
2018 P * 2017 Calculated Value
2019 P * 2018 Calculated Value
2020 P * 2019 Calculated Value
2021 P * 2020 Calculated Value

P is the percentage here

How can i do this by pure Access Query? I am not interested in Macros for now.


Thanks in advance

[June7]

Getting the value from another record in same table can be difficult. Review http://allenbrowne.com/subquery-01.html#AnotherRecord

Or try:

SELECT FY, Percent, Percent * Nz(DLookup("Percent", "tablename", "FY=" & [FY]-1),1) AS Prior FROM tablename;

[haissk]

Getting the value from another record in same table can be difficult. Review http://allenbrowne.com/subquery-01.html#AnotherRecord

Or try:

SELECT FY, Percent, Percent * Nz(DLookup("Percent", "tablename", "FY=" & [FY]-1),1) AS Prior FROM tablename;

No it did not work, it is erroring out

[June7]

The query works for me. What error do you get? Are FY and Percent number type fields? I presume you used your actual table name.

[haissk]

My Table "Test"

ID	FY	COLA
1	2015	0.045
2	2016	0.05
3	2017	0.05
4	2018	0.05
5	2019	0.05
6	2020	0.05
7	2021	0.05
8	2022	0.05
9	2023	0.05
10	2024	0.05
11	2025	0.05

Query I tried - SELECT FY, COLA, COLA * Nz(DLookup("COLA", "Test", "FY=" & [FY]-1),1) AS Prior FROM Test;

Result

FY	COLA	Prior
2015	0.045	#Error
2016	0.05	#Error
2017	0.05	#Error
2018	0.05	#Error
2019	0.05	#Error
2020	0.05	#Error
2021	0.05	#Error
2022	0.05	#Error
2023	0.05	#Error
2024	0.05	#Error
2025	0.05	#Error

[June7]

My test still works.

Source data:

FY	Percent
2015	0.045
2016	0.05
2017	0.05
2018	0.05
2019	0.05
2020	0.05

Query results:

FY	Percent	Prior
2015	0.045	0.045
2016	0.05	0.00225
2017	0.05	0.0025
2018	0.05	0.0025
2019	0.05	0.0025
2020	0.05	0.0025

However, I just realized you want 'cumulative'. That is not what my query does. That could be even harder and a report may be the best vehicle. Textbox in report has a Running Sum property. Use the query a RecordSource for report and set textbox Running Sum property.

[haissk]

Anyways the result what you got is not the one i am expecting. See below

FY	COLAValue	Expected
2015	0.045	1.05
2016	0.05	1.10
2017	0.05	1.15
2018	0.05	1.21
2019	0.05	1.27
2020	0.05	1.33
2021	0.05	1.40
2022	0.05	1.47
2023	0.05	1.54
2024	0.05	1.62
2025	0.05	1.70

based on the calculation as below
2015 P * 1 (Multiply the value by 1 for first year)
2016 P * 2015 Calculated value (the above value)
2017 P * 2016 Calculated Value
2018 P * 2017 Calculated Value
2019 P * 2018 Calculated Value
2020 P * 2019 Calculated Value
2021 P * 2020 Calculated Value

[June7]

I edited my previous post. Review again.

How do you get 1.05 for 2015? You said 0.045 should be multiplied by 1. Looks 1 was added.