Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    cjbuechler is offline Novice
    Windows Vista Access 2000
    Join Date
    May 2009
    Posts
    25

    Total based on Formula based on field value


    Hi,

    I have a table of transactions and I want to get that data into a report...easily done but I want to make a field in the report that is the result of a specific formula based on one of the fields in the transaction table.

    Rather than make a really long if statement I thought I could do something like the following (overly simplified):

    Transactions table....
    Transaction, Transamount, group
    1 10 1
    2 20 1
    3 30 3
    4 40 2

    Formula table...
    group, formula
    1 '[Transamount]*10'
    2 '[Transamount]*100'
    3 '[Transamount]*1000'

    and after I made a query that combined those two tables have a repot that would say.......

    Transaction Transtotal
    1 100
    2 200
    3 30000
    4 4000

    Is there an easy way of doing that or am I looking at being stuck with a long if statement for that field??

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Why not create your own Public Function in a standard module and pass it the TransAmount and Group values and return the results of the formula. You could use a Select Case structure on the Group value to determine the formula to use and it would be *much* easier to read, document and maintain.

  3. #3
    cjbuechler is offline Novice
    Windows Vista Access 2000
    Join Date
    May 2009
    Posts
    25
    Thanks, That makes sense. I'll go ahead and try that.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Great! Post back if you need any further help on this issue.

  5. #5
    cjbuechler is offline Novice
    Windows Vista Access 2000
    Join Date
    May 2009
    Posts
    25
    It's been ages since I built any functions. Would something like this work?


    Public Function Retains(ByVal fee As Double, Market As Double, formula As Double) As Double
    Select Case (formula)
    Case 1
    Retains = Market - fee * 1
    Case 2
    Retains = Market - fee * 2
    Case 3
    Retains = Market - fee * 3
    Case 4
    Retains = Market - fee * 4
    Case 99
    Retains = 0
    End Select
    End Function

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I don't know what sort of values you can expect in these three fields (Transaction, Transamount, group) but I suspect the first and last will not have a decimal value so using an Integer or Long would be more appropriate. The function will be in a standard module and Public so you can debug it from the immediate window until it compiles and then works as you want it to.

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I was not putting you off BTW. I'll be glad to stay with you until the function is complete and works as you want.

  8. #8
    cjbuechler is offline Novice
    Windows Vista Access 2000
    Join Date
    May 2009
    Posts
    25
    Oh sorry, I am only at that client once a week so I haven't had a chance to work on it more than where I left off the other day...

    I was able to make the function and my test formula's work when I use it in a query. I actually think this is solved since all I have left is to put the actual formulas in and they are pretty darn ugly.

    Thanks for all your help!

  9. #9
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Why not post the "pretty darn ugly" code and maybe we can clean it up a bit.

  10. #10
    cjbuechler is offline Novice
    Windows Vista Access 2000
    Join Date
    May 2009
    Posts
    25
    You asked for it

    These are my four formulas copied from the client's excel file without any clean up yet. I am passing F and M to the function. The code for the function itself works great and these are the only missing pieces.

    1
    =IF(M<200000,0.007/4*M,IF(M<500000,(M-200000)*0.0069/4+350,IF(M<1000000,(M-500000)*0.0065/4+867.5,IF(M<2000000,(M-1000000)*0.0059/4+1680,IF(M<3000000,(M-2000000)*0.0058/4+3155,IF(M<5000000,(M-3000000)*0.005/4+4605,(M-5000000)*0.004/4+7105,0))))))

    2
    =IF(M<1000000,0.001*M,IF(M<2000000,(M-1000000)*0.0011+1000,IF(M<3000000,(M-2000000)*0.0043/4+2100,IF(M<5000000,(M-3000000)*0.0035/4+3175,(M-5000000)*0.0025/4+4925,0))))

    3
    =IF(M<500000,0.001*M,500+0.00075*(M-500000))

    4
    =F-(IF(M>=200000,(F-16)-0.0011/4*M, IF(M>=100000,(F-16)-0.0013/4*M, (F-16)-((0.0015/4)*M))))

  11. #11
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You're right...pretty ugly. I will assume F means Fee and M means Market.

  12. #12
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    This should give you an idea what you can do. See if I got Formula 1 right:
    Code:
    Public Function Retains(InFee As Double, Market As Double, InFormula As Integer) As Double
       Select Case (InFormula)
          Case 1
             Select Case Market
                Case Is < 200000
                   Retains = Market * (0.007 / 4)
                Case Is < 500000
                   Retains = ((Market - 200000) * (0.0069 / 4)) + 350
                Case Is < 1000000
                   Retains = ((Market - 500000) * (0.0065 / 4)) + 867.5
                Case Is < 2000000
                   Retains = ((Market - 1000000) * (0.0059 / 4)) + 1680
                Case Is < 3000000
                   Retains = ((Market - 2000000) * (0.0058 / 4)) + 3155
                Case Is < 5000000
                   Retains = ((Market - 3000000) * (0.005 / 4)) + 4605
                Case Else
                   Retains = ((Market - 5000000) * (0.004 / 4)) + 7105
             End Select

  13. #13
    cjbuechler is offline Novice
    Windows Vista Access 2000
    Join Date
    May 2009
    Posts
    25
    Wow, thanks. I hadn't thought of using a nested case statement rather than a bunch of if's. That should make things easier.

  14. #14
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Quote Originally Posted by cjbuechler View Post
    Wow, thanks. I hadn't thought of using a nested case statement rather than a bunch of if's. That should make things easier.
    Both to implement and to maintain and document. Have fun.

  15. #15
    cjbuechler is offline Novice
    Windows Vista Access 2000
    Join Date
    May 2009
    Posts
    25
    Thanks for all your help! I really appreciate your time helpping and insite with this.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Select a certain field of a record based on ID
    By cesarone82 in forum Access
    Replies: 3
    Last Post: 06-06-2009, 01:16 PM
  2. Replies: 0
    Last Post: 06-03-2009, 10:25 PM
  3. query - criteria based on another row field value
    By techexpressinc in forum Queries
    Replies: 1
    Last Post: 05-29-2009, 04:17 PM
  4. Replies: 0
    Last Post: 04-17-2008, 09:24 AM
  5. Replies: 1
    Last Post: 10-26-2007, 07:29 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