Hi,
I am trying to come up with a solution for the following issue, and I need help! Please.

Basically, I have an Access table which holds billing forecast, the table attributes are:

[u]Example</u>:
Product : ProductX, ProductY...
Billing term: 12 or 24 or 36... (months)
Amount: $100 or $230....
Billing Date: 05/01/2008...


For accounting firms, there is a rule called the revenue recognition. For example, although the amount of $100 will be collect in May of 2009,this amount will be recognized over the course of the next 12 months or 24 months, it depends on the term. (BillingAmnt/BillingTerm)

To be more specific, here is an example:

[u]Input</u>:(Current)

   Product BillingAmnt BillingTerm BillingDate
   Prod-xyz $48 24 07/01/08

[u]Desired Output</u>:

   [u]Product</u> [u]RecognizedAmnt</u> [u]BillingTerm</u> [u]RecognitionDate</u>
   Prod-xyz $2 24 07/01/08
   Prod-xyz $2 24 08/01/08
   Prod-xyz $2 24 09/01/08
   ........
   Prod-xyz $2 24 06/01/09
                 ------
                  $48

Please note that the forecast table contains over half a million records that consist of different products,billing dates, billing amounts and billing terms.

Your help/hints are realy appreciated.

If you require more details, please let me know.

Thanks

How often do you need to run this job? This looks like something I would code with a disconnected recordset and store in a temp table to run your report from. It sounds like you want to aggregate these values for all records and show them for certain periods, is that correct? If so, do you also want to be able to select those preiods?

Anyway, if it is coded, you would want to run the report, and have time to wait for it. It might take several minutes. This would not be the kind of report every user could run at will.


mmcdonal

Look it up at: http://wrox.books24x7.com

Hi,
Thank you for the quick responce.

This is a monthly task and I would like to end-up with a table that will allow me to select different period of time.

Thanks again.

Here is a script that you can modify that will get you started.

I would create a table in the database, or somewhere (I am assuming a SQL back end) that had the fields you want in your report.

Then create a DELETE query to empty the table first before you run a report.

remember to turn warnings off and then back on as your run the delete query.

Then take the first record from your source table, and run this code on it, and AddNew into your target table each loop, or in a batch update:

'========================================
Product = "Prod-xyz"
BillingAmnt = 48
BillingTerm = 24
BillingDate = #07/01/2008#
i = 1
dtDate = BillingDate
RecAmnt = BillingAmnt / BillingTerm

Do Until i = BillingTerm + 1
        WScript.Echo "Product: " & Product
        WScript.Echo "RecAmnt: " & RecAmnt
        WScript.Echo "BillingTerm: " & BillingTerm
        WScript.Echo "RecognitionDate: " & DateAdd("m", i, BillingDate)

i = i + 1
Loop
'=========================================

Ignore the WScript.Echo statements. That is where you would add the new data to your recordset.

In this case, you would end up with 24 new records in your target table.

However, if you took a date parameter, like, show all records for July 2008, then you could add this inside the loop:

Do Until i = BillingTerm + 1
     If DatePart("m", BillingTerm) = 7 AND DatePart("yyyy", BillingTerm) = 2008 Then
        WScript.Echo "Product: " & Product
        WScript.Echo "RecAmnt: " & RecAmnt
        WScript.Echo "BillingTerm: " & BillingTerm
        WScript.Echo "RecognitionDate: " & DateAdd("m", i, BillingDate)
     End If
i = i + 1
Loop

Did that help?



mmcdonal

Look it up at: http://wrox.books24x7.com

Would you mind giving me more details? Please.

My VBA knowlege is limited.

Actually my background is in philosophy:)

Thank you for your help!

I was a Philosophy major, too. Think sentential calculus!

Here is what I would do:

Create a table with these columns:

tblTarget
Product - text
Amount - currency
BillingDate - date

Then create a DELETE query to clean out this table:

qryDELETE_Target
DELETE FROM tblTarget

Then create a form to run your report from that has two combo boxes:

cboMonth
Column 0
1
2
3
...
Column 1
Jan
Feb
Mar
...

cboYear
Column 0
2008
2009
2010
...

Then have the user select a Month and Year before running the report. Then, assuming you have the report built and ready to accept data, do this:

Dim rs_Source As ADODB.Recordset
Dim rs_Target As ADODB.Recordset
Dim sSQL_S As String
Dim sSQL_T As String
Dim sProduct As String
Dim iTerm As Integer
Dim lAmount As Long
Dim dtBDate As Date
Dim dtRDate As Date
Dim iMonth As Integer
Dim iYear As Integer
Dim i As Integer

iMonth = Me.cboMonth
iYear = Me.cboYear

sSQL_S = "SELECT * FROM tblMySourceTable"
sSQL_T = "SELECT * FROM tblTarget"

Set rs_Source = New ADODB.Recordset
rs_Source.Open sSQL_S, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Set rs_Target = New ADODB.Recordset
rs_Target.Open sSQL_T, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

rs_Source.MoveFirst

Do Until rs_Source.EOF
   sProduct = rs_Source("Product")
   iTerm = rs_Source("BillingTerm")
   lAmount = rs_Source("BillingAmount")
   dtBDate = rs_Source("BillingDate")
      dtRDate = dtBDate
   lRecAmnt = lAmount / iTerm
   i = 1

      Do Until i = iTerm + 1
           If DatePart("m", dtRDate) = iMonth AND DatePart("yyyy", dtRDate) = iYear Then
              rs_Target.AddNew
              rs_Target("Product") = sProduct
              rs_Target("Amount") = lRecAmnt
              rs_Target("BillingDate") = dtRDate
              rs_Target.Update
           End If
      dtRDate = DateAdd("m", i, dtBDate)
      i = i + 1
      Loop
rs_Source.MoveNext
Loop

DoCmd.OpenReport "rptYourReportName", acPreview...


Okay, this has not been tested. Please use a copy of the database to test this.

Does it work for you? It will take a long time to process because of the nested loops, but it will be accurate. You may want to export the resulting table to Excel instead of running a report that you will have to PDF to save.


mmcdonal

Look it up at: http://wrox.books24x7.com

I will try what you proposed and then let you know about the result!


I just wanted to say one thing!

Regardless to programming, regardless to computers and regardless to this entire world!

May God help you! Be with you and Grade you at all occasions!

I have asked questions few times, and I have seen nothing from you, but sincerity and dedication!

When I was a kid, my beloved mother taught me that: "God is the help of his slave servant, as long as he is in the help of other human beings!In the event he gets tired or bored from helping others, God will lift that blessing from him and give it to someone else who will do a better job!"

Thank you again

I meant Gard you!

Ok...
I think that Gard does not exist in the english Language. I think it is frensh from "Garder" which means protect.

En dit "Guard" en anglais. It means the same.

Thank you for the blessing. I will try to continue to deserve it.

If you have problems with the code, can you send me a sample copy of the database and I will work on it and send it back.

mmcdonal

Look it up at: http://wrox.books24x7.com