Solved

Help with Do Loop Correct order of events

Posted on 2014-03-11
4
279 Views
Last Modified: 2014-03-19
I need to determine the OverrideAmount Paid by comparing the Previous Years value to the quarterly event.

See ee post:  http://www.experts-exchange.com/Database/MS_Access/Q_28384010.html
for the first part of the equation.  works great, however per the  problem below  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:
so
if .34 <  Q1T1 (.1)  then = nOvramt = 0

else
.34 > .1 but < Q1T2 (.50) THEN  novramt = Sum( TotalNetUSExp * T2E)

cont to compare the .34 value against each Q1T??? until equation = true.

             If True then increment Q?T1, etc.

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

Open in new window

0
Comment
Question by:Karen Schaefer
  • 2
4 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 39922980
Your problem stems from not having a normalized table.  Any consideration of normalizing that table?

Year  Quarter    Tier   ValueField
2012      1            1             x
2012      1            2             x

This structure would make it significantly easier to do this type of comparison.

Do the values in this matrix reflect sales performance, or something like that?  If so, you might want to actually use 6 columns to represent this:

Year  Quarter    Tier      MinVal     MaxVal     Multiplier
2014      1            1            .44            .50              
2014      1            2            .50           1.45
2014      1            3          1.45           3.00

Where multiplier is the value you are multiplying by the value TotalNetUSExp (T1E, T2E, ...)

Then to get the Multiplier you would simply use a query or a DLookup within that table for the record where the value you are testing for falls between the Min and Max values (actually >=MinVal AND < MaxVal)
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39923490
where did the .34 value come from?  I thought you were using .385 in your example.
0
 

Assisted Solution

by:Karen Schaefer
Karen Schaefer earned 0 total points
ID: 39923836
aikimark,
where did the .34 value come from?  

It was just an example I used not an actual value, then I went back an reposted the actual values.


Dale,

Unforunately, this is the way the data is, it comes from a datawarehouse.  I inherited the data   The entire mdb is not normalizated, not referential intregity.  a real mess, but I not here to fix the issues just create a report based on existing data.

Got any ideas on how to loop thru the data to get the results.

Where multiplier is the value you are multiplying by the value TotalNetUSExp (T1E, T2E, ...)

these are additional fields within the same table.  The Q?T? fields contain a percentage that I need to compare to the previous years percentage(PY).  and if PY is compared until the Q?T? value is greater than the PY for that quarter - to return an dollar amount based on the above mention equation.

Normalization, How would you go about accomplishing this task?

I had this thought last night.

I under an extreme deadline I only have 1.5 weeks left on this assignment.  I could really use your help.

thanks,

K
0
 

Author Closing Comment

by:Karen Schaefer
ID: 39939102
thanks
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now