Create Query to calculate month over month decrease

[Tabasco77]

I'm trying to create a query that will effectively allow me to plug in a forfeiture rate per month, and the original shares as of a given date, and then have it calculate all future months based on that.

So ideally, i'll have a table with all of the future months, and the forfeiture rate per month (assume 1%). Another table will have the #of shares as of today (assume 3/31/2022 & 1,000). The forfeiture rate will be applied to the prior month's ending units, so it will change over time. The query will give me the following:

Month	Ending Shares
3/31/2022	1000
4/30/2022	990
5/31/2022	980.1
6/30/2022	970.29

There must be a way to accomplish this right?

[Gicu]

You would need something like a ranking query (or row numbering) using the month-end dates as the unique ids:

https://stackoverflow.com/questions/...crosoft-access

https://bytes.com/topic/access/insig...-ms-access-sql

[Tabasco77]

Not sure I follow or if this will give me exactly what I want. I don't see how the ranking will actually calculate 1000 * .01 for April, then use 990 * .01 to calculate May, then 980.1 to calculate June. It's more like a repeated query so each month is calculated off of the prior month. Again, could totally be that I'm just missing something here...

The other thing to mention is the forfeiture rate can change month over month, so it's not necessarily going to stay a constant.

[Gicu]

I was thinking at something like this.

Cheers,

[Tabasco77]

That is absolutely amazing!!! Thank you very much for sorting this out for me! Saved me hours of scratching my head!

[Gicu]

You're very welcome!
Cheers,