Solved

How to spread money over several months in Access

Posted on 2013-01-29
15
277 Views
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.

Thanks,
Brian
demo.xlsx
0
Comment
Question by:KollBrian
  • 9
  • 3
  • 2
15 Comments
 
LVL 77

Expert Comment

by:peter57r
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..
0
 

Author Comment

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

Expert Comment

by:peter57r
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.
0
 

Author Comment

by:KollBrian
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....
0
 
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.

JeffCoachman
0
 

Author Comment

by:KollBrian
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 :)
Database1.accdb
0
 

Author Comment

by:KollBrian
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).
Database1.accdb
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 74

Expert Comment

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

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

Author Comment

by:KollBrian
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).
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38865440
OK,

I'll look at this tonight
0
 

Author Comment

by:KollBrian
ID: 38885646
Hi Boag2000,

Any thoughts on this little puzzle yet?

Brian
0
 

Accepted Solution

by:
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
        .MoveFirst
        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
            Else
               
                monthsCount = 0
                currentMonthlyDate = .Fields(CoverageFromDateField)
                Do
                    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
                Debug.Print
            End If
            Set rsDest = CurrentDb.OpenRecordset(DestinationTable, dbOpenDynaset, dbSeeChanges)
            For monthIdx = 1 To monthsCount
                rsDest.AddNew
                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))
                Else
                    ' 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)
                rsDates.MoveFirst
                rsDest(FY_AssignField) = rsDates.Fields(FY)
                rsDates.Close
                Set rsDates = Nothing
                'Blank out the "To" date.
                rsDest(CoverageToDateField) = Null
                rsDest.Update
            Next monthIdx
            rsDest.Close
            Set rsDest = Nothing
            .MoveNext
        Loop
    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
0
 

Author Comment

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

Author Closing Comment

by:KollBrian
ID: 39103771
n/a
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now