Results 1 to 2 of 2
  1. #1
    bg18461 is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    2

    Need help with code logic/consolidation

    I am trying to calculate load factor for a data table that changes monthly in access. For those who are unfamaliar with calculating Load Factor, the formula is: kwh/(kw*(days in month) *24)

    My data is rolling over 24 months and I calculate the data quarterly. For the first quarter this is my code.

    Code:
    Function FirstQtrLFMth1to12(dThisMonth As Date, AKWH1 As Long, AKWH2 As Long, AKWH3 As Long, _
    AKWH4 As Long, AKWH5 As Long, AKWH6 As Long, AKWH7 As Long, AKWH8 As Long, AKWH9 As Long, _
    AKWH10 As Long, AKWH11 As Long, AKWH12 As Long, AKW1 As Long, AKW2 As Long, AKW3 As Long, _
    AKW4 As Long, AKW5 As Long, AKW6 As Long, AKW7 As Long, AKW8 As Long, AKW9 As Long, _
    AKW10 As Long, AKW11 As Long, AKW12 As Long) As Long
    Dim sDate As String
    Dim sYear As String
        sYear = CStr(Year([dThisMonth]))
        sDate = Month([dThisMonth]) & "/1/" & sYear
     
        Select Case sDate
            Case "1/1/" & sYear
                FirstQtrLFMth1to12 = (AKWH1 / (AKW1 * DaysM("1/1/" & sYear) * 24))
            Case "2/1/" & sYear
                FirstQtrLFMth1to12 = ((AKWH2 / (AKW2 * DaysM("2/1/" & sYear) * 24)) _
                + (AKWH1 / (AKW1 * DaysM("1/1/" & sYear) * 24))) / 2
            Case "3/1/" & sYear
                FirstQtrLFMth1to12 = ((AKWH3 / (AKW3 * DaysM("3/1/" & sYear) * 24)) _
                + (AKWH2 / (AKW2 * DaysM("2/1/" & sYear) * 24)) _
                + (AKWH1 / (AKW1 * DaysM("1/1/" & sYear) * 24))) / 3
            Case "4/1/" & sYear
                FirstQtrLFMth1to12 = ((AKWH4 / (AKW4 * DaysM("4/1/" & sYear) * 24)) _
                + (AKWH3 / (AKW3 * DaysM("3/1/" & sYear) * 24)) _
                + (AKWH2 / (AKW2 * DaysM("2/1/" & sYear) * 24))) / 3
            Case "5/1/" & sYear
                FirstQtrLFMth1to12 = ((AKWH5 / (AKW5 * DaysM("5/1/" & sYear) * 24)) _
                + (AKWH4 / (AKW4 * DaysM("4/1/" & sYear) * 24)) _
                + (AKWH3 / (AKW3 * DaysM("3/1/" & sYear) * 24))) / 3
            Case "6/1/" & sYear
                FirstQtrLFMth1to12 = ((AKWH6 / (AKW6 * DaysM("6/1/" & sYear) * 24)) _
                + (AKWH5 / (AKW5 * DaysM("5/1/" & sYear) * 24)) _
                + (AKWH4 / (AKW4 * DaysM("4/1/" & sYear) * 24))) / 3
            Case "7/1/" & sYear
                FirstQtrLFMth1to12 = ((AKWH7 / (AKW7 * DaysM("7/1/" & sYear) * 24)) _
                + (AKWH6 / (AKW6 * DaysM("6/1/" & sYear) * 24)) _
                + (AKWH5 / (AKW5 * DaysM("5/1/" & sYear) * 24))) / 3
            Case "8/1/" & sYear
                FirstQtrLFMth1to12 = ((AKWH8 / (AKW8 * DaysM("8/1/" & sYear) * 24)) _
                + (AKWH7 / (AKW7 * DaysM("7/1/" & sYear) * 24)) _
                + (AKWH6 / (AKW6 * DaysM("6/1/" & sYear) * 24))) / 3
            Case "9/1/" & sYear
                FirstQtrLFMth1to12 = ((AKWH9 / (AKW9 * DaysM("9/1/" & sYear) * 24)) _
                + (AKWH8 / (AKW8 * DaysM("8/1/" & sYear) * 24)) _
                + (AKWH7 / (AKW7 * DaysM("7/1/" & sYear) * 24))) / 3
            Case "10/1/" & sYear
                FirstQtrLFMth1to12 = ((AKWH10 / (AKW10 * DaysM("10/1/" & sYear) * 24)) _
                + (AKWH9 / (AKW9 * DaysM("9/1/" & sYear) * 24)) _
                + (AKWH8 / (AKW8 * DaysM("8/1/" & sYear) * 24))) / 3
            Case "11/1/" & sYear
                FirstQtrLFMth1to12 = ((AKWH11 / (AKW11 * DaysM("11/1/" & sYear) * 24)) _
                + (AKWH10 / (AKW10 * DaysM("10/1/" & sYear) * 24)) _
                + (AKWH9 / (AKW9 * DaysM("9/1/" & sYear) * 24))) / 3
            Case "12/1/" & sYear
                FirstQtrLFMth1to12 = ((AKWH12 / (AKW12 * DaysM("12/1/" & sYear) * 24)) _
                + (AKWH11 / (AKW11 * DaysM("11/1/" & sYear) * 24)) _
                + (AKWH10 / (AKW10 * DaysM("10/1/" & sYear) * 24))) / 3
            Case Else
                FirstQtrLFMth1to12 = 0
     
        End Select
     
    End Function
    I have 4 functions (1 for each quarter), my last quarter is


    Code:
    Function FourthQtrLFMth1to12(dThisMonth As Date, AKWH1 As Long, AKWH2 As Long, AKWH3 As Long, _
    AKW1 As Long, AKW2 As Long, AKW3 As Long)
    Dim sDate As String
    Dim sYear As String
        sYear = CStr(Year([dThisMonth]))
        sDate = Month([dThisMonth]) & "/1/" & sYear
     
        Select Case sDate
            Case "10/1/" & sYear
                FourthQtrLFMth1to12 = (AKWH1 / (AKW1 * DaysM("10/1/" & sYear) * 24))
            Case "11/1/" & sYear
                FourthQtrLFMth1to12 = ((AKWH2 / (AKW2 * DaysM("11/1/" & sYear) * 24)) _
                + (AKWH1 / (AKW1 * DaysM("10/1/" & sYear) * 24))) / 2
            Case "12/1/" & sYear
                FourthQtrLFMth1to12 = ((AKWH3 / (AKW3 * DaysM("12/1/" & sYear) * 24)) _
                + (AKWH2 / (AKW2 * DaysM("11/1/" & sYear) * 24)) _
                + (AKWH1 / (AKW1 * DaysM("10/1/" & sYear) * 24))) / 3
            Case Else
                FourthQtrLFMth1to12 = 0
     
        End Select
     
    End Function
    I would like to consolidate my code to this

    Code:
    Function QtrLFMth13to24(dThisMonth As Date, ParamArray lngLF() As Variant) As Long
    Dim i As Integer
    'lngLF() is zero based...
        i = Month(dThisMonth) - 1
    'function should only receive 16 array elements (6 months data) at a time ...
        If i > 5 Then i = i - 6
        QtrLFMth13to24 = lngLF(i) + lngLF(i + 1) + lngLF(i + 2)
    End Function
    I used the above code for simple functions like adding, but the load factor requires more math and I am not sure how to institute it, any ideas?

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    i would assume something like this you might want to pay for in order to get a product that is professional

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Consolidation DB
    By dimitrz in forum Database Design
    Replies: 7
    Last Post: 03-23-2010, 12:44 PM
  2. Replies: 8
    Last Post: 02-24-2010, 01:49 PM
  3. Split database logic
    By Overdive in forum Database Design
    Replies: 3
    Last Post: 02-05-2010, 08:22 AM
  4. Access 2003 code vs Access 2007 Code
    By ralphjramirez in forum Access
    Replies: 5
    Last Post: 11-23-2009, 12:33 PM
  5. Help with VBA Code
    By access.newby in forum Forms
    Replies: 1
    Last Post: 11-15-2009, 05:43 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums