Complex Do Loop/For Each. (1 Viewer)

[kfschaefer1]

I have to loop thru 2 recordsets.

I need to first determine the quarter , then retrieve a value from each of the 6 fields for that quarter and compare that value against a previous years value that is also stored in the table for each quarter.

the following is the field names not the field value.

Tier 1 2 3 4 5 6 PrYr
Qtr1 Q1T1, Q1T2, Q1T3,Q1T4, Q1T5, Q1T6 .385
Qtr2 Q2T1, Q2T2, Q2T3.Q2T4, Q2T5, Q2T6 1.25
Qtr3 Q3T1, Q3T2, Q3T3,Q3T4, Q3T5, Q3T6 .774
Qtr4 Q4T1, Q4T2, Q4T3,Q4T4, Q4T5, Q4T6 .333

if Qtr1 = Q1T1 = 0.44 Q1T2 = .50, Q1T3 = 1.45, Q1T4 = 3.00, Q1T5 = .25, Q1T6 = 6.0

So I need to be able to set the value of

PrYR = .385 and compare against the value of the 1st qtr for each tier for Qtr1.

Then go to Qtr2 and repeat the process but grap the Q2 PRYR value = 1.25 and compare against all Tiers for Qtr2.

As so forth for each quarter.

then I need to compare the value of the PrYr and if it is the following then


If PctYrlyIncrease< Tier1 Pct (Q1T1) Payout = ‘0’
elseIf PctYrlyIncrease> Tier1 Pct (Q1T1) and < Q1T2 then
Sum(TotalNetUSExp * T1E)
ElseIf PctYrlyIncrease> Tier2 Pct (Q1T2) and < Q1T3 then
Sum( TotalNetUSExp * T2E)

and repeat for each Tier per Each Qtr.

Here is my code so far:

Public Function BkOvrCalc(ByVal gContractID As String) As Long
Dim curDB As DAO.Database
Dim strSQL As String, strSQL1 As String
Dim rs As DAO.Recordset, rs1 As DAO.Recordset, rs2 As DAO.Database
Dim strField As String, strNextField As String
Dim strfld As String, strfldNext As String
Dim nQTR As Long, nTr As Long
Dim nfld As String, nfld1 As String, nfld2 As String
Dim x As Integer
Dim nQTRno As String
Dim nTierNo As String
Dim nOvrAmt As Currency

   On Error GoTo BkOvrCalc_Error

Set curDB = CurrentDb

    curDB.Execute ("Delete * from tblSummaryExpectationDetail")
    curDB.Execute ("Delete * from tblOverride_ExpectQtrlyTotals")
    curDB.Execute ("qrySummaryExpectation_Detail")
   
    strSQL = "Select * from tblSummaryExpectationDetail" & _
            " Where ContractNumber = " & Chr(34) & gContractID & Chr(34) & ""
    Set rs = curDB.OpenRecordset(strSQL)
    
    strSQL1 = "Select * from TblContracts" & _
        " Where ContractNumber = " & Chr(34) & gContractID & Chr(34) & ""
    Set rs1 = curDB.OpenRecordset(strSQL1)
    
    'rs.MoveFirst
    Do Until rs.EOF
        ' Override Code Type
        x = rs1.Fields("ORType")
        nfld1 = rs.Fields("PctYrlyIncrease")
        nQTR = rs.Fields("Quarter")
        Debug.Print "nqtr: " & nQTR
        Select Case x ' OverRide Type
            Case 1 'Quarters
                While rs1.EOF = False
                    If nQTR > 1 Then
                      nfld1 = DLookup("PctYrlyIncrease", "tblSummaryExpectationDetail", "Quarter = " & nQTR & "")
                    End If
                        nQTRno = "Q" & nQTR
                        'Do Until Right(strNextField, 1) = 6
                            For nTr = 1 To 5
                                'determine which Quarter Tier value to use
                                nTierNo = "T" & nTr
                                strField = nQTRno & nTierNo
                                Debug.Print "strField: " & strField
                                strNextField = nQTRno & "T" & nTr + 1
                                Debug.Print "strNextField: " & strNextField
                                'Determine the % Payout:
                                strfld = "T" & nTr & "E"
                                strfldNext = "T" & nTr + 1 & "E"
          Debug.Print "nfld1: " & nfld1
          Debug.Print "PYr: " & rs1.Fields(strField).Value
                                    If nfld1 <= 0 Then
                                        nOvrAmt = 0
                                        BkOvrCalc = nOvrAmt
                                    ElseIf nfld1 < rs1.Fields(strField).Value Then
                                        nOvrAmt = 0
                                        BkOvrCalc = nOvrAmt
                                    ElseIf nfld1 > rs1.Fields(strField).Value = True Then
                                        If nfld1 < rs1.Fields(strNextField).Value = True Then
                                            nOvrAmt = rs.Fields("TotalNetUSExp") * rs1.Fields(strfld).Value
                                            GoSub Calc
                                        End If
                                    End If
                             Next nTr
                       ' rs1.MoveNext
                       'Loop
                      '  'Next nTr
                     nQTR = nQTR + 1
                Wend
            Case 2 'Annual Flat%
            Case 3 'Annual Flat$
        End Select

        rs.MoveNext
    Loop

Calc:

    BkOvrCalc = nOvrAmt
Return
    Set rs = Nothing
    Set rs1 = Nothing
    Set rs2 = Nothing
    rs1.Close
    rs1.Close
    rs2.Close

   On Error GoTo 0
   Exit Function

BkOvrCalc_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & _
        ") in procedure BkOvrCalc of Module basUtilities"
End Function

 

[Galaxiom]

Your problem is firmly rooted in the strucutal mistake of storing the data in separate field by quarter.

The values should all be in the same field with another field to indicate the quarter and year. Querying then becomes simple.

Many developers will use a a date (either begiining or end) to indicate the quarter.

 

[kfschaefer1]

this is the data I get from the data warehouse, and I need to compare values within an access query, hence the need to loop thru the various fields to determine the value I am looking for. It comes accross in a huge text file - and since it is a datawarehouse normalization does not exists.

I am just doing a project to create a few reports based on the DW data.

any ideas on how to proceed.

also note that once a quarters value meets one of the criteria below than I need stop and move onto the next Quarter.

Just to be clear I need to look at Quarter (1) and compare the values of the Tiers for the Quarter(1) PvYr .

Hope you can help.

Thanks,

K

 

[kfschaefer1]

Also I am unable to retrieve the next value for the Previous Year for the next quarter.

Once Qtr1 has finished I need to be able to go to Qtr2 and retrieve the PvYr value for Qtr2.

ContractNumber Quarter PctYrlyIncrease
00003977 1 0.00%
00003977 2 125.88%
00003977 3 167.54%
00003977 4 40.20%