historical monthly report

[Matthew Snook]

I have a database with data recorded along with the date of an event. Current state of the database can be reported using the most recent data, and monthly reports can be generated for any particular month.

Now what I need is an historical monthly report. For every month back to the origin of the database I need to know the state of the system at that time. If data occured every month I could use a "GroupBy" clause to generate the report. But in some months no data is entered, and I still want to know the state of the system, ie. the most recent data at that time. How do I programmatically list every year and month? Do I need to create a table with Year and Month, or is there a way to have a report summarize in this fashion without having a physical list of the months. It just seems that there would be a more elegant method, but I don't know what it is!

Any ideas?

Thanks,

Matthew

 

[antomack]

The following should help to do what you require.

Public Function MonRep(dteRun As Date)

Dim strSQL As String
Dim db As DAO.Database
Dim rstData As DAO.Recordset
Dim int1stYear As Integer, int1stMonth As Integer, intCurYear As Integer, intCurMonth As Integer

' assuming table called Data with date field called TranDate
strSQL = "SELECT min(Trandate)as FirstDate FROM Data;"

Set db = CurrentDb
' open a recordset with strSql
Set rstData = db.OpenRecordset(strSQL)

' get first year and month
int1stYear = Year(rstData.FirstDate)
int1stMonth = Month(rstData.FirstDate)
'get current year and month
intCurYear = Year(dteRun)
intCurMonth = Month(dteRun)

' delete records from report table
' contains Year, Week and Summed/Averaged/?? Fields
' table needs to be created manually with required fields
strSQL = "DELETE * from RepMonths"
' do delete using strSQL
db.Execute strSQL

' populate report table with Years and Months from first to current
Do While int1stYear < intCurYear + 1
' to exclude the current month from the table remove '+ 1' after intCurMonth in line below
Do While int1stMonth < 13 And (int1stYear & Format(int1stMonth, "00")) < ((intCurYear) & (Format(intCurMonth + 1, "00")))
strSQL = "INSERT INTO RepMonths ( AYear, AMonth) "
strSQL = strSQL & "VALUES (" & int1stYear & ", " & int1stMonth & ");"
' move to next month
db.Execute strSQL
int1stMonth = int1stMonth + 1
Loop
' move to next year
int1stYear = int1stYear + 1
int1stMonth = 1
Loop

' compile data by month for use in report
' do a GroupBy Query grouped on month as if every month was available
' something like
strSQL = "SELECT Year([TranDate]) AS Year, Month([TranDate]) AS Month, Sum(Data.data2) AS SumOfdata2 "
strSQL = strSQL & "FROM Data GROUP BY Year([TranDate]), Month([TranDate]);"
Set rstData = db.OpenRecordset(strSQL)

' update report table with details from rstData
Do While Not rstData.EOF
' update RepMonths where Year and Month equal values for current rstData record
strSQL = "UPDATE RepMonths SET RepMonths.FieldName = " & rstData.CalcField & ""
strSQL = strSQL & "WHERE (((RepMonths.AYear)=" & rstData.Year & ") AND ((RepMonths.AMonth)=" & rstData.Month & "));"
db.Execute strSQL
' move to next record
rstData.MoveNext
Loop

rstdata.close
db.close

' open report based on RepMonths table
'DoCmd.OpenReport "MonthlyHistoric"

End Function

 

[Matthew Snook]

Antomack:

Thanks for the reply! It will take me a bit to interpret what you've done here...

Is this your own construction?

Thanks again,

Matthew

P.S.
It looks great! I'll probably save the insert until the final sql statement, since I have to group by some other data pulled from the original "Data" table, but it looks like a pretty good plan. I previously have not created a table simply for use of a report, but if the shoe fits...

Matt