I am composing a database to track my revenue each month and quarter. I have created a very simple form, with three data fields:
date billed out - date field
date collected - date field
amount collected - currency field
The number I enter is a cummulative number. It represents the amount collected (as of the 'date collected') for the month billed out. ie:
date billed - Oct 2011
date collected - Feb 2012
amount - $52423.46
This means that of all the money we billed out in Oct 2011, $52k had been collected as of Feb 2012
Here is what I'd like to be able to calculate:
cummulative collection for month collected - I know that it needs to be able to identify the correct records (the month in question), then grab the correct data field (amount) and then add it up.
net collection for month - this is simply the cummulative amount from that month, minus the cummulative amount from the month before
quartly total - this would be the net total from a three month period
And obviously I am going to try to format this information into a report that is easily readable. I am nowhere versed enough to write this formula. I would appreciate any help here. I haven't been able to play with all of the starter solutions yet but does anyone know if there is a ready solution out there? If not, I am willing to do this from stratch with a little direction.
I am willing to share the data I have already entered if it would help. There are no identifying data in it so it's ok.