Advertisement

07.23.2008 at 02:07AM PDT, ID: 23587754
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!
8.9

Array - Sales report

Asked by peterdarazs in Visual Basic Programming

Hi Experts

Can someone please help with  \sales values being carried over to the next month\.

I had a function that calculated total sales, shown by month , by rep
I now want to calculate just the sales that our letters are generating (in a 40day period after the letter), also shown by month by rep.  

Essentially what I have done is place a loop around the original (totalsales) report thereby limiting the original section to calculate sales per individual letter within it's 40day period. It's doing that very nicely on a letter by letter basis, but I'm having trouble stacking all the letter's sales reports onto each other.  As it stands here only the lowest month (eg -2) is correct. Thereafter every month starts off with the previous months value and adds to it.
Is there a way to stop the prev months total getting carried over, (without deleting whatever may already exist in that next months array )
eg
Mnth    Rep1   Rep2..........
-2         $54       $100
-1         $54       $150
0            $54       $ 150

should read out:

Mnth    Rep1   Rep2..........
-2         $54       $100
-1         $0         $50
0            $0       $ 0

A long bit of code, but I'm just asking for a general guide. Many thanks

Peter (will check in in a couple of hours and then tomorrow morn)Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:

Dim SalesRpt As Recordset 'sum of sales fees by month , by rep
Dim Letters As Recordset 'All letters + date written
'
Dim MthNos As Integer
Dim Rep1 As Single
Dim Rep2 As Single
Dim Rep3 As Single
Dim Rep4 As Single
Dim RepOther As Single
'
'
Dim X As Integer
Dim Y As Integer
'
Dim ClientId As Integer
Dim Fromdate As String ' date letter was written
Dim Todate As String    'until 40 days after last letter was written
 
    
    'clear salesrpt
    For X = 1 To UBound(SalesRpt, 1)
        For Y = 1 To UBound(SalesRpt, 2)
            SalesRpt(X, Y) = ""
        Next
    Next
 
'
    'select LETTERS records
    Set Letters = CURDB.OpenRecordset("Select * from LETTERS where Isdate(PC_Letterdate) = true order by (PC_Letterdate) asc", dbOpenDynaset)
    Letters.MoveFirst
    
'********************************************************************
'Loop through Letters & calculate sales within 40 days of each correspondence
'********************************************************************
 
Do While Not Letters.EOF
    'ClientId
    ClientId = Letters("PC_ClientID")
    'Fromdate
    Fromdate = Letters("PC_LetterDate")
    'ToDate - + 40 days
    Todate = DateAdd("d", 40, Fromdate)
    
    '****original report  (total sales , by month , by rep )******
    'Adapted to only select sales to a client within 40days of a letter (ie fromdate and todate)
    SQL = ""
    SQL = "Select Year([SH_ServDate]) as Yr, Month([SH_ServDate]) as Mth, [SH_RepID] as Doctor"
    SQL = SQL & ", first(DateDiff('m', Now, [SH_ServDate])) as MthNr"
    SQL = SQL & ", Sum([SH_Fee]) as SalesFee"
    SQL = SQL & " from SALES_HISTORY inner join LETTERS"
    SQL = SQL & " on SALES_HISTORY.[SH_ClientId] = LETTERS.[PC_ClientID]"
    SQL = SQL & " where [SH_ServDate] > datevalue('" & Fromdate & "')"
    SQL = SQL & " and [SH_ServDate] < datevalue('" & Todate & "')"
    SQL = SQL & " and [SH_ClientId] = " & ClientId
    SQL = SQL & " and [SH_ItemNos] in ('21','23','53','54','721','723','725','2620','2517','10991','10993','10990')"
    SQL = SQL & " group by Year([SH_ServDate]), Month([SH_ServDate]), SH_RepID"
    SQL = SQL & " order by Year([SH_ServDate]), Month([SH_ServDate]), SH_RepID"
    ':
    Set SalesRpt = CURDB.OpenRecordset(SQL, dbOpenSnapshot)
    DoEvents
    '
    On Error Resume Next
    SalesRpt.MoveLast
    
    
    If SalesRpt.RecordCount = 0 Then
        'move to next letter
    Else
        SalesRpt.MoveFirst
    End If
    
    MthNos = SalesRpt("MthNr")
    
    'place sales generated by this letter into array. Arranged (by rep, by month)
    Do
        
        If MthNos = SalesRpt("MthNr") Then
            
            If InStr(1, UCase$(SalesRpt("Rep")), "REP1") > 0 Then
                    Rep1 = Rep1 + SalesRpt("SalesFee")
                
            ElseIf InStr(1, UCase$(SalesRpt("Rep")), "REP2") > 0 Then
                    Rep2 = Rep2 + SalesRpt("SalesFee")
                
            ElseIf InStr(1, UCase$(SalesRpt("Rep")), "REP3") > 0 Then
                    Rep3 = Rep3 + SalesRpt("SalesFee")
                
            ElseIf InStr(1, UCase$(SalesRpt("Rep")), "REP4") > 0 Then
                    Rep4 = Rep4 + SalesRpt("SalesFee")
                
            Else
                    RepOther = RepOther + SalesRpt("SalesFee")
                
            End If
            '
        End If
            '
        'keep/print previous months totals in array
        SalesRpt((MthNos + 25), 1) = CStr(Format(Rep1, "Currency"))
        SalesRpt((MthNos + 25), 2) = CStr(Format(Rep2, "Currency"))
        SalesRpt((MthNos + 25), 3) = CStr(Format(Rep3, "Currency"))
        SalesRpt((MthNos + 25), 4) = CStr(Format(Rep4, "Currency"))
        SalesRpt((MthNos + 25), 5) = CStr(Format(RepOther, "Currency"))
 
 
        'print on screen
        IMPList.AddItem MthNos & vbTab _
        & CStr(Format(Rep1, "Currency")) & vbTab & vbTab _
        & CStr(Format(Rep2, "Currency")) & vbTab & vbTab _
        & CStr(Format(Rep3, "Currency")) & vbTab & vbTab _
        & CStr(Format(Rep4, "Currency")) & vbTab & vbTab _
        & CStr(Format(RepOther, "Currency"))
 
            
        'reset totals for new month
        MthNos = SalesRpt("MthNr")
        Rep1 = 0
        Rep2 = 0
        Rep3 = 0
        Rep4 = 0
        RepOther = 0
        Extra40 = 0
        
       
        'add sales to existing array
        If MthNos = SalesRpt("MthNr") Then
            
            If InStr(1, UCase$(SalesRpt("Rep")), "REP1") > 0 Then
                Rep1 = Rep1 + SalesRpt("SalesFee")
            
            ElseIf InStr(1, UCase$(SalesRpt("Rep")), "REP2") > 0 Then
                    Rep2 = Rep2 + SalesRpt("SalesFee")
                
            ElseIf InStr(1, UCase$(SalesRpt("Rep")), "REP3") > 0 Then
                    Rep3 = Rep3 + SalesRpt("SalesFee")
                
            ElseIf InStr(1, UCase$(SalesRpt("Rep")), "REP4") > 0 Then
                    Rep4 = Rep4 + SalesRpt("SalesFee")
                
            Else
                    RepOther = RepOther + SalesRpt("SalesFee")
                
            End If
        End If
 
        SalesRpt.MoveNext
        '
    Loop Until SalesRpt.EOF 'gets sales totals by month for current letter
    
    'keep totals in array
    SalesRpt((MthNos + 25), 1) = CStr(Format(Rep1, "Currency"))
    SalesRpt((MthNos + 25), 2) = CStr(Format(Rep2, "Currency"))
    SalesRpt((MthNos + 25), 3) = CStr(Format(Rep3, "Currency"))
    SalesRpt((MthNos + 25), 4) = CStr(Format(Rep4, "Currency"))
    SalesRpt((MthNos + 25), 5) = CStr(Format(RepOther, "Currency"))
 
 
    'print on screen
    IMPList.AddItem MthNos & vbTab _
    & CStr(Format(Rep1, "Currency")) & vbTab & vbTab _
    & CStr(Format(Rep2, "Currency")) & vbTab & vbTab _
    & CStr(Format(Rep3, "Currency")) & vbTab & vbTab _
    & CStr(Format(Rep4, "Currency")) & vbTab & vbTab _
    & CStr(Format(RepOther, "Currency"))
 
 
Letters.MoveNext
Loop 'moves to next letter
 
 
SalesRpt.Close
DoEvents
[+][-]07.23.2008 at 02:43AM PDT, ID: 22067433

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.23.2008 at 05:24AM PDT, ID: 22068360

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.23.2008 at 05:42AM PDT, ID: 22068532

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zone: Visual Basic Programming
Sign Up Now!
Solution Provided By: GrahamSkan
Participating Experts: 1
Solution Grade: B
 
 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628