Chapter 7 Exercise 31

31. Create a table to hold revenue earned per week, using a date format of yyyy-ww. Include a column to hold percentage change from the prior week. Write a query to compute the totals and a routine to compute and store the percentage change.

Private Sub cmdCompute_Click()
'On Error GoTo Err_cmdCompute_Click

    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandType = adCmdText
    cmd.CommandText = "DELETE FROM RevenueChange"
    cmd.Execute
    
    cmd.CommandText = "INSERT INTO RevenueChange(YearWeek, Revenue) SELECT YearWeek, SumOfAmountCharged FROM Ch07Ex26"
    cmd.Execute
    
    Dim oldWeek As String
    Dim oldValue As Currency
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    
    Dim sSQL As String
    sSQL = "SELECT YearWeek, Revenue, PercentChange FROM RevenueChange ORDER BY YearWeek "
    rst.Open sSQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic
    
    oldWeek = rst("YearWeek")
    oldValue = rst("Revenue")
    Dim ChangeValue As Currency
    Dim ChangeWeeks As Integer
    If Not rst.EOF Then rst.MoveNext
    
    Dim pctChange As Single
    
    Do Until rst.EOF
        Dim w1 As Integer, w2 As Integer
        ChangeWeeks = ConvertDateToWeek(rst("YearWeek")) - ConvertDateToWeek(oldWeek)
        ChangeValue = rst("Revenue") - oldValue
        If (ChangeValue = 0) Or (ChangeWeeks = 0) Then
            pctChange = 0
        Else
            pctChange = ChangeValue / oldValue / ChangeWeeks
        End If
        rst("PercentChange") = pctChange
        rst.Update
    
        rst.MoveNext
    Loop
    rst.Close

Exit_cmdCompute_Click:
    Exit Sub

Err_cmdCompute_Click:
    MsgBox Err.Description
    Resume Exit_cmdCompute_Click
    
End Sub