Query to Calculate Monthly Depreciation

[e_black]

Hey All,



I've got an issue that I've not been able to resolve over the last week of trying different things. Hoping I could get some pointers on what's the best approach to implementing a solution in MS Access

I have a simple data table tracking depreciation value and schedule for a number of items. Simplified example below:

EQUIPMENT	TOTAL VALUE	DEPRECIATION START	DEPRECIATION END
Pen	$10000	01/03/2018	30/06/2018
Pencil	$15000	01/06/2018	31/08/2018

I am trying to put together a query that would massage the above data into a table that tells me how much money is being depreciated on a monthly basis. Simplified output below:

	March	April	May	June	July	August
Pen	$2500	$2500	$2500	$2500
Pencil				$5000	$5000	$5000

I thought a Crosstab would be perfect, but I can't get it to consider a date range as represented in my data table. I also tried building a query that would create a row for each month that each item was due for depreciation. I was then going to feed this into another query which aggregated the data. However I couldn't get this to work either.

Has anyone had experience with a similar problem and what is the best approach to achieve the desired result that I am after?

Thanks for any help.

[ranman256]

Crosstab is perfect for the final output.
but the monthly records would look like:
Pen, 3/1/18, 2500
Pen, 4/1/18, 2500
etc.

then youd run the crosstab on that.

[e_black]

Thanks ranman! Uncanny timing, but today I started working on the approach you suggested - I managed to setup a separate year/month lookup table, and then used a query to reference the lookup table to determine which months have applicable depreciation. Just got that to work earlier, which gave me the output you suggested: Pen, 31/18, 2500.

Then I used a crosstab to marry it all together.

It's currently close to where I want it to be. The crosstab groups things by month so currently January 2019 comes before December 2018. I have a bit of tweaking to do there.

I'll post back with the final solution once I've (hopefully) got it all working as planned.

Cheers