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

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.

Thanks,
Brian
demo.xlsx
0
KollBrian
• 9
• 3
• 2
1 Solution

Commented:
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 Commented:
Would exporting the data to Excel make it any easier?  Is Excel more flexible for this?
0

Commented:
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 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....
0

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

Author 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).
Database1.accdb
0

MIS LiasonCommented:
...and what about all the steps I posted...?

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

Author 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).
0

MIS LiasonCommented:
OK,

I'll look at this tonight
0

Author Commented:
Hi Boag2000,

Any thoughts on this little puzzle yet?

Brian
0

Author 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 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

CurrentDb.Execute ("Delete * from " & DestinationTable)
.MoveFirst
Do Until .EOF
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
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
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.
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 Commented:
so feel free to close the question out.
0

Author Commented:
n/a
0
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.