• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 338
  • Last Modified:

How to spread money over several months in Access

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.

  • 9
  • 3
  • 2
1 Solution
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..
KollBrianAuthor Commented:
Would exporting the data to Excel make it any easier?  Is Excel more flexible for this?
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.
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

KollBrianAuthor Commented:
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....
Jeffrey CoachmanMIS LiasonCommented:
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.

KollBrianAuthor Commented:
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 :)
KollBrianAuthor Commented:
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).
Jeffrey CoachmanMIS LiasonCommented:
...and what about all the steps I posted...?

I open the Db ....and then what...?
KollBrianAuthor Commented:
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).
Jeffrey CoachmanMIS LiasonCommented:

I'll look at this tonight
KollBrianAuthor Commented:
Hi Boag2000,

Any thoughts on this little puzzle yet?

KollBrianAuthor Commented:
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
KollBrianAuthor Commented:
so feel free to close the question out.
KollBrianAuthor Commented:
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now