How to spread money over several months in Access

Posted on 2013-01-29
Medium Priority
Last Modified: 2013-04-23
Hi Team,

I have a datatable that contains several columns including "ext price", "master system", "system", "coverage From date", and "coverage to date".

So the table has the total amount I spent on something (i.e. what the check amount was), and the beginning and end dates of the coverage (it's service contracts).

My leader wants to see how much I spent "per month" for each system. So if I wrote a $2,000 check in June and that check covers maintenance for say... July 2013 to June 2014 he wants a report that show $166.66/month for that system starting in July and ending in the next June column.

I have figured out how to calculate the number of months in the coverage period, and how to use that to calculate the monthly "amount" per transaction but how do I create a report (or pivot table) that shows that amount each month instead of lumping it into the first month?

In "financial" terms I think this is referred to showing the money as a yearly accural...?

I am attaching a very simple spreadsheet view of the data.

Question by:KollBrian
  • 9
  • 3
  • 2
LVL 77

Expert Comment

ID: 38831837
This is a bit of a difficult requirement to deal with.

The difficulty is that it requires the creation of multiple records per item - one per month with its monthly amount.  And if you want the sum of the monthly amounts to match a pre-defined total then you also have to agree how this will be handled - do you adjust the first month, or the last month or what.
A total of 123.45 over 12 months might require 11 amounts of 10.28 and one of 10.37 for example,  or it could be 11 @ 10.29 and one @ 10.26 - someone has to define a rule for how this is to be done.
In Access you will need a vba procedure to deal with the creation of the records.

You can then use a crosstab query or pivot table to get the horizontal display, which would be pretty simple to do..

Author Comment

ID: 38831969
Would exporting the data to Excel make it any easier?  Is Excel more flexible for this?
LVL 77

Expert Comment

ID: 38832015
I would have said so, if the volumes are not too great.

You would be managing each total on a separate row, I guess and if you are trying to show monthly totals then you obviously have to deal with this in the layout of each item.

 You still have to address the last/first amount prtoblem but it is easier to calculate and manipulate the values in each row.
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.


Author Comment

ID: 38832883
ok, what about this.  Is there a sql formula I can use in a "create table" query, that would take the existing data (coverage_from, coverage_to, num_months, month_price) and populate a "new" table with the data so I dont have to sit there and manually populate the data?

so for example 2 purchases.  Purchase 1, Jan 2012 to Dec 2012, cost per month $166.  Purchase 2, March 2012 to August 2013, cost per month $550.  So the sql formula would then take the 166 populate the data rows for Jan 2012, feb 2012, mar 2012, etc.  and then take the second purchase and populate rows for mar 2012, april 2012, may 2012, etc.

I can modify the sql to also copy the other needed data, but I am unsure how to make the sql populate the rows based on start/stop dates....
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38857092
Remember, all of this is a bit difficult to visualize with out any context.

Can you post a sample of your db as it stand now?
Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Unhide any hidden database objects
10. Compile the code. (From the VBA code window, click: Debug-->Compile)
11. Run the compact/Repair utility.
12. Remove any Passwords and/or security.
13. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
14. Post the explicit steps to replicate the issue.
15. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.


Author Comment

ID: 38864634
Thats odd, I could swear I posted the response this am...

ok, well apologies for the delay, work has been rather nuts.

Here is an updated sample of the DB.  key table is "tbl_Monthly_Spent" and the key query is "qry_sub_Actually_Spent_no_FY_filter Query".

Brian :)

Author Comment

ID: 38864701
apologies (yet again) I just noticed the request for data included a sample "output" as well.

This version of the DB now includes a "SAMPLE_FINISH_TABLE" where I took just one instance and made a sample replication (manually via excel and copy/paste).
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38864780
...and what about all the steps I posted...?

I open the Db ....and then what...?

Author Comment

ID: 38865398
I thought all the steps were leading to, "make sure we can just open and go"... :)

So, if you look at the "sample_finish_table" thats what I hope the formula will be able to achieve (database wide).

so the flow is:
---open qry_sub_Actually_Spent_no_FY_filter in design view, set "greater than" date in Coverage_From_Date column (as I am only worried about FY12 and forward in the real database)
--save qry_sub_Actually_Spent_no_FY_filter query and close
--double click the qry_sub_Actually_Spent_no_fy_filter_by_month query and answer yes, yes, yes to the three "are you sure" questions

--that will yield a freshly populated "tbl_monthly_spent".

From there, I want to create a new "make table query" that populates a table like "SAMPLE_FINISH_TABLE".  then I can go ahead and create a pivot table to sum everything up in nice monthly columns by master system, system, venue, month.  (similar to the current pvt_Spending_By_System_Forecast-Actual_Isolated report).
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38865440

I'll look at this tonight

Author Comment

ID: 38885646
Hi Boag2000,

Any thoughts on this little puzzle yet?


Accepted Solution

KollBrian earned 0 total points
ID: 39090650
Ok, finally found an answer :)
The solution is a VBA script that my friend helped write.  Now it populates a table just perfectly and I can run the reports off the new table.

Here is the VB script:
Public Sub createActualMonthlyAmounts()
    ' Table Names
    Const SourceTable As String = "tbl_qry_sub_Actually_Spent_Table"
    Const DestinationTable As String = "tbl_sub_Actual_Spend_By_Month"
    ' Field names.
    Const ExtPriceField As String = "Ext_Price"
    Const CoverageFromDateField As String = "Coverage_From_Date"
    Const CoverageToDateField As String = "Coverage_To_Date"
    Const PoNumberField As String = "PO_#"
    Const LineNumberField As String = "Line_#"
    Const FY_AssignField As String = "FY_Assign"
    Const FY As String = "FY_#"
    Dim rsSource As DAO.Recordset
    Dim rsDest As DAO.Recordset
    Dim rsDates As DAO.Recordset
    Dim sql As String
    Dim currentMonthlyDate As Date
    Dim monthlyStartDate() As Date
    Dim monthsCount As Integer
    Dim monthIndex As Integer
    Dim monthlyDollarAmount As Double
    Dim i As Integer
    Dim destFieldIdx As Integer
    Dim monthIdx As Integer
    Dim errorsFound As Integer

    sql = "SELECT * from " & SourceTable
    errorsFound = 0
    Set rsSource = CurrentDb.OpenRecordset(sql)
    CurrentDb.Execute ("Delete * from " & DestinationTable)
    With rsSource
        Do Until .EOF
            Debug.Print rsSource.Fields(ExtPriceField), rsSource.Fields(CoverageFromDateField), rsSource.Fields(CoverageToDateField)
            If IsNull(.Fields(CoverageFromDateField)) Or IsNull(.Fields(CoverageToDateField)) Or IsNull(.Fields(ExtPriceField)) Then
                'MsgBox ("Coverage From and To Dates and Ext_Price are not all populated for PO " & rsSource(PoNumberField) & " - Line " & rsSource(LineNumberField) & ".")
                errorsFound = errorsFound + 1
                monthsCount = 0
                currentMonthlyDate = .Fields(CoverageFromDateField)
                    monthsCount = monthsCount + 1
                    ReDim Preserve monthlyStartDate(monthsCount)
                    monthlyStartDate(monthsCount) = currentMonthlyDate
                    currentMonthlyDate = DateAdd("m", 1, currentMonthlyDate)
                    monthlyDollarAmount = Round(100 * .Fields(ExtPriceField) / monthsCount) / 100
                Loop Until currentMonthlyDate > .Fields(CoverageToDateField)
                For i = 1 To monthsCount
                    Debug.Print monthlyStartDate(i)
                Next i
                Debug.Print "Months = " & monthsCount & "   MonthlyAmount=" & monthlyDollarAmount
            End If
            Set rsDest = CurrentDb.OpenRecordset(DestinationTable, dbOpenDynaset, dbSeeChanges)
            For monthIdx = 1 To monthsCount
                For destFieldIdx = 0 To rsDest.Fields.Count - 1
                    'For each field in the destination, look up the same field by name in the source and copy it.
                    rsDest.Fields(destFieldIdx) = rsSource.Fields(rsDest.Fields(destFieldIdx).Name)
                Next destFieldIdx
                'Replace the Ext_Price and Coverage_From_Date with calculated monthly values.
                If monthIdx = monthsCount Then
                    ' Final month gets whatever is left over to cancel out rounding.
                    rsDest(ExtPriceField) = rsSource(ExtPriceField) - (monthlyDollarAmount * (monthIdx - 1))
                    ' Non-final months get rounded to the penny.
                    rsDest(ExtPriceField) = monthlyDollarAmount
                End If
                rsDest(CoverageFromDateField) = monthlyStartDate(monthIdx)
                sql = "select [FY_#] from tbl_Date where fy_date = #" & rsDest(CoverageFromDateField) & "#"
                Set rsDates = CurrentDb.OpenRecordset(sql)
                rsDest(FY_AssignField) = rsDates.Fields(FY)
                Set rsDates = Nothing
                'Blank out the "To" date.
                rsDest(CoverageToDateField) = Null
            Next monthIdx
            Set rsDest = Nothing
    End With
    If errorsFound > 0 Then
        MsgBox ("Coverage From and To Dates and Ext_Price are not all populated on " & errorsFound & " rows.  These were not inserted into " & DestinationTable & ".")
    End If
End Sub

Author Comment

ID: 39090654
so feel free to close the question out.

Author Closing Comment

ID: 39103771

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

586 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question