Results 1 to 8 of 8
  1. #1
    BeeZeRCoX is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2011
    Posts
    4

    Post Complicated set of calculations based on transaction IDs, transaction value, etc.

    Hey all,

    I work for an organization and we're trying to figure out how to calculate and assign some end user fees.

    Typing this out is a little tough so I'll do the best I can. We have an event registration system that uses a ticketmaster type of model where the user pays a fee at the very end. We are currently using this registration system for sports camps and are having some trouble assigning user fees. The fee is dependent upon the total amount of the transaction. This is very simple for a single transaction, but becomes increasingly difficult when multiple camps are registered for at once.

    Fee structure example:
    $1-$99 - 5% + $1.50
    $100-$250 - 4% + $2.00
    $251+ - 3% + 3.00

    Obviously, our system automatically calculates the TOTAL transaction fee, but we need to assign the weighted transaction fee individually to each item of a transaction. Each item is cataloged in the output individually, but if two camps were registered and paid for in the same transaction, they are given the same transaction ID. Example below:

    Transaction ID CAMP Cost TOTAL FEE
    1 Tennis $50 $8.00
    1 Baseball $100 $8.00

    2 Basketball $80 $5.50


    3 Tennis $50 $11.25
    3 Baseball $100 $11.25
    3 Hockey $125 $11.25


    As you can see, we get the total fee for each, but I'd like to assign an individual fee to each. See Transaction 1: the total fee for both is $8.00. I'd like to calculate that total fee based on the total amount of the two items ($150) then calculate the fee for each item based on its weighted amount towards the total transaction (Tennis: $50 - 33.33333% of total transaction - fee: $2.67 / Baseball: $100 - 66.66666667 of total transaction - fee: $5.33).

    I would like to come up with a way to automatically calculate this.

    Thoughts?

    Thanks for your help - please let me know if you need clarification on any of these.

    In addition, if you think this is better suited for Excel as opposed to Access, please let me know. We would prefer Access.

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Welcome to the forum!

    What you describe can be done in Access as long as your tables are set up correctly. In contrast to Excel, you generally do not store calculated values in tables, but just do the calculation on the fly when you need it.

    You will need a table to hold the fee structure

    tblFeeStructure
    -pkFeeStrID primary key, autonumber
    -spFeePercent (your percentage amount)
    -currAddOn (the extra dollar amount added on)
    -currLowerLimit (the lower dollar amount limit of the range)
    -currUpperLimit (the upper dollar amount limit of the range)

    Technically speaking, since each fee has two range limit values (upper and lower), the above structure is not completely normalized. If you choose, you can separate the limit values into a separate but related table if you want it to be fully normalized.

    You would then use a textbox control in a form or report to calculate the appropriate fee. You could also do this in a query using a calculated field.

    It will be a little tricky in that you will have to get the sum of all records with the same transID and then use that sum to get the appropriate fee info. You will also need the sum to determine the ratio to apply to the fee. i.e. 50/150*(fee amount). You will probably need to use the DSum() and DLookup() functions within Access to do this.

    So in short, what you want can be done, but it will take a little effort to get there.

  3. #3
    BeeZeRCoX is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2011
    Posts
    4
    Thanks for the warm welcome.

    It sounds like you definitely understand what I'm trying to accomplish - which is a good thing. I appreciate the advice. I will continue to work on this and if you have any further insight please don't hesitate to let me know.

  4. #4
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Since the key to this (as well as to any successful database) is the table structure, do you have that part set up? If so, is it something you can share by posting it (with any sensitive data removed or altered)? Since you are new to the forum (<10 posts), I believe that you will have to zip the database before you can post it.

  5. #5
    BeeZeRCoX is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2011
    Posts
    4
    Thanks for the help again.


    I think I have these tables set up properly. I took out a lot of the columns within this table that are of no consequence, including those that contained personal information. Please see attached.

  6. #6
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Looking at the DB you posted, I had a recommendation. It is best not to have spaces or special characters in your table or field names.

    On a structural side, you have 3 fees in your main table: sub event fee, donation processing fees and registration processing fees. If there are more than 1 fee associated with a transaction, that describes a one-to-many relationship which requires a separate but related table. Also, in Access you calculate totals when you need them, so you would not need the total processing fees field. I restructured you example database accordingly, migrate the fees to the new table (just the sub event fee since that was the only field with non-zero values). I left the append query I used to do that for illustration purposes. I also established the relationships in the relationship window.

    Next are the queries.

    First, create a query that gets the sub event fee data from the revised structure. I have done this with qryGetSubEventFeeData.

    I then use that query in qryGetSumByTransactionID to get the sum of the sub event fees for each transaction

    I then use qryGetSumByTransactionID along with a couple of sub queries to get the corresponding fee percent and add on. I then use those values to get the actual fee for each transaction. (see query: qryGetTheFeePercentAndAddON)

    I'll let you digest that before going on to get the breakdown of the fee for each individual sub event. Feel free to try it on your own too.

    DB attached.

  7. #7
    BeeZeRCoX is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2011
    Posts
    4
    Thanks. This is great. I should've taken the other "fees" out - I apologize for not doing so. The one ultimately we need to be concerned with is the Subevent fee.

    I'm going to look this over - I appreciate the help.

  8. #8
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    The last step would be to create another query that joins the query qryGetTheFeePercentAndAddON back to the query qryGetSubEventFeeData. You will need to join by the transaction number field in both, they you can set up calculated fields to get the ratio and the final fee per line item.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Copy Transaction and Send to another table
    By jo8701 in forum Access
    Replies: 1
    Last Post: 08-16-2011, 08:46 AM
  2. Transaction v Lookup table
    By jke in forum Access
    Replies: 2
    Last Post: 06-15-2011, 08:44 PM
  3. SQL Transaction question
    By Mazdaspeed6 in forum Programming
    Replies: 4
    Last Post: 12-16-2010, 12:51 PM
  4. Latest Transaction Report
    By Worm in forum Reports
    Replies: 3
    Last Post: 08-16-2010, 06:10 AM
  5. Urgent: Need to make a transaction form
    By Sheharyar_rr in forum Access
    Replies: 1
    Last Post: 10-31-2009, 10:52 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