Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Nov 2011
    Posts
    103

    Unanswered: To sum the amount of corresponding years

    Ok. I ran into another issue trying automate my database fields.

    I have a Forms named CalendarYrAmt with yearly date fields and corresponding amounts. It looks something like this.

    BegYear1: 2004 BegAmt1: 1504.11 EndYear1: 2005 EndAmt1: 7495.88
    BegYear2: 2005 BegAmt2: 1604.39 EndYear2: 2006 EndAmt2: 7995.63
    BegYear3: 2006 BegAmt3: 1704.66 EndYear3: 2007 EndAmt3: 8495.34
    BegYear4: 2007 BegAmt4: 1804.93 EndYear4: 2008 EndAmt4: 8995.06
    BegYear5: 2008 BegAmt5: 1905.21 EndYear5: 2009 EndAmt5: 9494.80

    I need my calculation (in VBA probably) to look into all BegYear fields and EndYear fields and if same years exist combine the corresponding Amt if not just copy and paste the year and amount onto a blank field correspondingly.

    The blank fields are named Year1 and Year1Amt.

    If I were to visually input how I would want the data to look it would be like this.

    Year1: 2004 Year1Amt: 1504.11
    Year2: 2005 Year2Amt: 9100.27 (7495.88 + 1604.39)
    Year3: 2006 Year3Amt: 9700.29 (7995.63 + 1704.66)
    Year4: 2007 Year4Amt: 10300.27 (8495.34 + 1804.93)
    Year5: 2008 Year5Amt: 10900.27 (8995.06 + 1905.21)
    Year6: 2009 Year6Amt: 9494.80

    There are more than 1 record and all records will have different beginning Year and ending Year and their corresponding amount. Can this be done? If so can someone show me how?

    Thanks.

  2. #2
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Not sure what you mean by 'if not just copy and paste the year and amount onto a blank field correspondingly'...

    Are you talking about saving the results to another table?

    Or, are you simply trying to produce the results visually on your form, and not save them anywhere?


    I'm a little unsure what you want to happen exactly, so a little more info would be helpful.
    Looking for the perfect beer...

  3. #3
    Join Date
    Nov 2011
    Posts
    103
    Sorry. I'm talking about the way in my sample how Year1 and Year 6 doesn't need any calculation since there isn't another matching year.

    The results will be saved onto a table and the data will be pulled only when certain reports are ran.

    If you need any more information please let me know. I know I suck at writing out my thoughts.

    Thanks.

  4. #4
    Join Date
    Nov 2011
    Posts
    103
    Also the parenthesis were added to show the calculation. I don't want them to show in the actual form.

    Showing with calculation that would be going on in the background.

    Year1: 2004 Year1Amt: 1504.11
    Year2: 2005 Year2Amt: 9100.27 (7495.88 + 1604.39)
    Year3: 2006 Year3Amt: 9700.29 (7995.63 + 1704.66)
    Year4: 2007 Year4Amt: 10300.27 (8495.34 + 1804.93)
    Year5: 2008 Year5Amt: 10900.27 (8995.06 + 1905.21)
    Year6: 2009 Year6Amt: 9494.80


    The way the data should look in the form.

    Year1: 2004 Year1Amt: 1504.11
    Year2: 2005 Year2Amt: 9100.27
    Year3: 2006 Year3Amt: 9700.29
    Year4: 2007 Year4Amt: 10300.27
    Year5: 2008 Year5Amt: 10900.27
    Year6: 2009 Year6Amt: 9494.80

  5. #5
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Well, personally I would change the layout of your first table (you didn't give a name for it).

    I'd seperate the BegYear and BegAmt into one table.

    The EndYear and EndAmt into another.

    The reason being that first of all, it just makes it a lot easier to work with. Database tables are meant to hold ONLY DIRECTLY RELATED data. Having one record (row) with two different years will just give you a headache... Another problem is that you'll end up with NULL values, that you won't be able to get rid of without coding a lot more checks. For example. If you have a record in your table that looks like:

    Code:
    | BegYear |  BegAmt | EndYear | EndAmt |
     ---------+---------+---------+--------
    |   2009  | 9494.80 |         |        |
    So if you tell it to exclude NULL values in both fields, you'll end up cutting them off, or you'll end up with an empty result on your form/reports, which will make things look messy. It's not totally unavoidable, but it is a pain, and an unnecessary one.

    Separate them out. You'll be thankful you did as time goes on.


    As for the code, I'm still not ENTIRELY sure what you're after, as you talk about tables and forms interchangeably.

    If I understand correctly, you want to SAVE the results of your SUMs (BegAmt+EndAmt), when the BegYear and EndYear are equal, into a new table (called resTable(?)).

    I also don't understand why you're writing:

    Year1: 2004 Year1Amt: 1504.11

    Do you actually mean you just want the end user to see:

    2004 1504.11
    2005 9100.27
    etc. etc...

    As I can't see any benefit from having 'Year1:', 'Year2:', etc. will provide.
    Looking for the perfect beer...

  6. #6
    Join Date
    Nov 2011
    Posts
    103
    It's not going into a new table. The end result (the sum of begyear and endyear) will be calculated on the form. That's all I want it to do. To do the calculations on the form.

    I can't seperate the begyear and endyear since I need the 2 do the calculations. The user will only see the end result and not the begyears and endyears.

    If I were to seperate them out how would they look? How would I do the calculations without the begyear and endyear appearing in the same form?

    I think you're just about there to understand what I'm asking for. Sorry it's been confusing. The Year1 is shown because every record has different start year and end year. So 1 record might have Year1: 2004 while next record will have Year1: 2011. Some records will have Year1 to Year40. There are certain reports that user will need which will require the number of years on a record along with actual year that it's associated with.

    I hope things are bit more clearer now... :P

    Thanks.

  7. #7
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Quote Originally Posted by tobabygu View Post
    If I were to seperate them out how would they look? How would I do the calculations without the begyear and endyear appearing in the same form?
    Ahh, I think you misunderstand what the form is doing. The form just allows you to view and manipulate the data held in tables. You could allow your form to view and manipulate a hundred tables of data, all together if you so wished.

    If you want those to be summed, and the results displayed you could use some code like:

    Code:
    Private Sub generate_Click()
    
    Dim strSQL As String
    Dim tmpStr As String
    Dim i As Long
    Dim amt1 As Double
    Dim amt2 As Double
    Dim amt3 As Double
    Dim rs As DAO.Recordset
    Dim rs2 As DAO.Recordset
    
    'Sets up the listbox
    Me.results.RowSourceType = "Value List"
    Me.results.RowSource = vbNullString
    Me.results.ColumnCount = 2
    
    'Find every year in the table
    strSQL = "SELECT DISTINCT BegYear AS [Year]" & _
             "FROM YoUr_TaBlE_nAmE_hErE " & _
                "UNION " & _
             "SELECT DISTINCT EndYear AS [Year]" & _
             "FROM YoUr_TaBlE_nAmE_hErE"
    
    Set rs = CurrentDb.OpenRecordset(strSQL)
    
    If Not ((rs.BOF) And (rs.EOF)) Then
    
        rs.MoveLast
        rs.MoveFirst
        
        Do While Not rs.EOF
        
            'Get first number
            strSQL = "SELECT BegAmt " & _
                     "FROM YoUr_TaBlE_nAmE_hErE " & _
                     "WHERE BegYear = '" & rs![Year] & "'"
                     
            Set rs2 = CurrentDb.OpenRecordset(strSQL)
            
            'Save it to amt1 variable
            If Not ((rs2.BOF) And (rs2.EOF)) Then
                amt1 = rs2![BegAmt]
            Else
                amt1 = 0
            End If
            
            'Tidy
            rs2.Close
            Set rs2 = Nothing
            
            
            'Get second number
            strSQL = "SELECT EndAmt " & _
                     "FROM YoUr_TaBlE_nAmE_hErE " & _
                     "WHERE EndYear = '" & rs![Year] & "'"
                     
            Set rs2 = CurrentDb.OpenRecordset(strSQL)
            
            'Save it to amt2 variable
            If Not ((rs2.BOF) And (rs2.EOF)) Then
                amt2 = rs2![EndAmt]
            Else
                amt2 = 0
            End If
            
            'Tidy
            rs2.Close
            Set rs2 = Nothing
            
            
            'Calculate total
            amt3 = amt1 + amt2
            
            
            'Display result
            tmpStr = rs![Year] & ";" & CStr(amt3)
            Me.results.AddItem (tmpStr)
            
            
            'Increases the count
            i = i + 1
            
            
            'Continue the loop
            rs.MoveNext
            
        Loop
        
    End If
    
    'Tidy
    rs.Close
    Set rs = Nothing
    
    End Sub
    However, as you can see, that's quite a lot of code. Mainly because you haven't separated the the BegYear and BegAmt from the EndYear and EndAmt, which requires additional checks.

    You also didn't say exactly how you wanted the results displayed, so this dumps them in a listbox for viewing.

    I've attached my mini database, so you can look over it a little more.

    The above also assumes that the data is stored in a table called: YoUr_TaBlE_nAmE_hErE

    Which will obviously need changing.

    I'm finishing work for the day now, but let me know how you got on tomorrow.

    All the best.
    Attached Files Attached Files
    Looking for the perfect beer...

  8. #8
    Join Date
    Nov 2011
    Posts
    103
    Wow, that attachment is really close to what I want. I created another table and form in your attachment and attaching it back onto here.

    The table and the form that I created is almost just like the way my actual table and forms are set up. I wish I could just upload the my database onto here so you can have a better understanding but there are some security issues which doesn't allow me to do that.

    Yes, your code is confusing. I'm learning VBA so I'm not nearly good enough to understand what you are doing on that code you posted onto here.

    Hopefully my form and table will make sense to you. I was pretty blown away by what you did on your attachement. Basically I'm end calculation would auto fill into the Year1 and Year1Amt etc fields when BegYear1, BegAmt1, EndYear1, EndAmt1 etc are filled in and are focused.

    Thanks for all the help you have provided so far. It gets me closer to my desired database than I thought I would be at this point without turning to this forum.

    Also, have you tried Samuel Smith Oatmeal Stout? It's my favorite.

  9. #9
    Join Date
    Nov 2011
    Posts
    103
    Forgot my attachement...
    Attached Files Attached Files

  10. #10
    Join Date
    Nov 2011
    Posts
    103
    Here's maybe better attachment. It's basically the same but it contains more than one record. Pretty basic.
    Attached Files Attached Files

  11. #11
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Ok, I've found a little bit of time to look over your DB.

    One thing that immediately jumps out at me is that you have a finite number of boxes available for the totals.

    This isn't a problem if you're never going to have more than six years to contend with... However, if you could potentially be dealing with seven or eight years, then your going to run into trouble.

    The wondrous thing about programming is that it allows you to deal with 'unexpected' (so to speak) results. i.e., you might want to display 20 years worth of results one minute, then the next only want 5 years, then the next want 200 years. Which, with the way your form is setup at the moment, would be a problem.

    The code and example I put forward allows for this, and if you change the table to containing 20 years worth of data, then the listbox will still function, displaying all 20 years of data.

    I would strongly advise moving away from displaying results in textboxes, as that approach is far from future proof, and nine times out of ten, you'll have to revisit old work (normally long after you've forgotten exactly how it works), and try and modify it to handle something you could've simply allowed for in the first place.


    If you're ONLY EVER going to be looking at a maximum of six years, then I can provide you with some code and comments to show you how to achieve that.

    If you're going to be looking at a POTENTIALLY INFINITE number of years, then I would suggest going with a listbox (creating controls, resizing forms, and aligning everything dynamically through VBA is a huge pain [although not impossible], which is why I suggest a listbox, as all that stuff is taken care of for you).

    The code I provided can easily be adapted to have column heads (for Year / Amount) and a new incremental column that states 'Year 1', 'Year 2', etc.

    So you'd end up with something like:

    Code:
    | Year Number | Year | Amount |
     -------------+------+--------
    |    Year 1   | 2004 | 1946.2 |
    |    Year 2   | 2005 | 7622.7 |
    Which to me seems a lot better really, and is the only way to go if you have a dynamic amount of years you require summing up.

    Let me know what you need, and I'll provide you with what you need.


    PS: After looking a little more at your table layout, I think you could be looking at a lot of trouble down the road... However, I guess as I don't know what it's used for, how long its intended to be used, who's going to be using it, etc., etc., then it could be fine. I personally just dislike fields like [Year1] [Year2], as it doesn't give my scope for scaling if and when required. If 6 years is finite though, I imagine you'll be fine.
    Last edited by kez1304; 11-30-11 at 11:50.
    Looking for the perfect beer...

  12. #12
    Join Date
    Nov 2011
    Posts
    103
    Yes, that may be an issue if I can't get any more than 6 years. At most I'll have 40 years and at least I'll have 5 years.

    The only issue that I have with your example in which results are calculated in the list box is that I'm not sure if I can pull the results out of the list box and use them with any number of reports that will be looking for it. I also have around 150 records that the results need to be calculated and be ready for use at any given point.

    Does your example allow for this? If so I'll be more than happy to use it.

    Is there no way an IFF statement or a Case statement (or anything else I really don't care... ) can be used to look into all the BegYear and EndYear fields and if the years are the same combined the corrsponding BegAmt and EndAmt then order them in chronological order by year?

    And I'm sure you're right. I'll probably run into a lot of mess because of by database design, but I've put a lot of time into this and I didn't see it any other way.

    Thank you so much for you help kex1304. Hopefully there's a way that I can get the results that I want.

  13. #13
    Join Date
    Nov 2011
    Posts
    103
    No luck? Can anyone think of way to get this done???

    Thanks.

  14. #14
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Sorry buddy, must of missed your thread in my reply notices...

    Quote Originally Posted by tobabygu View Post
    Yes, that may be an issue if I can't get any more than 6 years. At most I'll have 40 years and at least I'll have 5 years.
    If this is the case, you're definitely going to have to use some for of dynamic control, such as a listbox or a combobox.

    Quote Originally Posted by tobabygu View Post
    The only issue that I have with your example in which results are calculated in the list box is that I'm not sure if I can pull the results out of the list box and use them with any number of reports that will be looking for it.
    Yeah, this isn't a problem... retrieving data from a listbox is its primary function.

    Quote Originally Posted by tobabygu View Post
    I also have around 150 records that the results need to be calculated and be ready for use at any given point.
    Not quite sure I follow you here... 150 records where? What do you mean by 'ready for use at any given point'?

    Quote Originally Posted by tobabygu View Post
    Does your example allow for this? If so I'll be more than happy to use it.
    If the data has been calculated, then it's been calculated. The results exist, you just use them wherever they're needed.

    Quote Originally Posted by tobabygu View Post
    Is there no way an IFF statement or a Case statement (or anything else I really don't care... ) can be used to look into all the BegYear and EndYear fields and if the years are the same combined the corrsponding BegAmt and EndAmt then order them in chronological order by year?
    This is possible, but not with your table layout as it stands. As I said to you before, you'll encounter problems, as SQL looks at each record as an individual entity, not fields across multiple records. To be able to do what you're after purely with SQL would require you to alter your tables. If it is possible with just SQL, it's way out of my league... VBA is a much simpler method of achieving these results IMHO.

    Quote Originally Posted by tobabygu View Post
    And I'm sure you're right. I'll probably run into a lot of mess because of by database design, but I've put a lot of time into this and I didn't see it any other way.
    As above...


    What exactly have you got to do with the results? I can show you how to manipulate the data stored in the listbox if that's what you're after.

    Or, you could just save the results straight to a table... As I said, once the data is calculated, you can use it. I showed you how they can be calculated, so now you just use them as you would any other piece of data.
    Looking for the perfect beer...

  15. #15
    Join Date
    Nov 2011
    Posts
    103
    There are 150 records. The attachment I sent you had 2 records. I have 148 more like it in a table that when a report is produced the results of the calculations need to be shown. Certain reports will require results of more than 1 records. I hope that makes more sense. I'm trying so hard make this understandable...

    How would you change the table format? Suggestions?

    How can I saved the results to the table?

    Thanks for the quick reply. I really appreciate your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •