Results 1 to 7 of 7
  1. #1
    MickFlanagen is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Apr 2013
    Posts
    11

    Excel Template to Access Conversion/Storage - Should I even use access for this?

    Hello all,

    I apologize in advance for my ignorance, but I have a very general question I am hoping someone can help me with.

    I've done a bunch of reading and started to put together the structure of my database, but I'm starting to wonder if access is even a good tool for the problem I am trying to solve.

    What I have now is an excel template (with ~12 worksheets) that many regional offices use to enter in some lease data, from which the excel sheet creates a rental schedule and does a whole ton of calculations on that data. Some are NPV calculations, some are yields etc etc.

    Eventually, I would like to:



    1) Enable users to fill out one of these templates, and save the data to the database (Just the inputs? All the data? My reading suggests just the inputs)
    2) Use the database to produce one of these templates for any lease in our system (shouldn't be hard, from what I've read)
    3) Sum up calculations from this template for many records (eg. if a tenant has many leases, what is the NPV of all of those leases, or what is the total NPV for all tenants)

    Although I have never really used Access, the reading I did suggested that it would be a good candidate to help me do these things.

    Here is my concern: my understanding is that access is not really designed to perform the types of calculations I need for #3. As long as I have the inputs (from Access) I will have no problem using them in the excel version, but does it make sense to use Access given that I may need to somehow be switching back and forth to get the info I want for my various reports? Can these even be done? Am I underestimating Access?

    I am very comfortable writing macros in VBA for excel, so if that's the solution, that is no problem. I assume what I need for #1 and #2 is a macro to arrange the inputs from the excel sheet into a format that can easily be dropped into access tables and vice versa.

    I hope this is the right place to post, and that my question makes sense. If not, please let me know.

    Thanks in advance,

    Mick

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I offer my experience in converting Excel calcs to Access for you to consider. I wanted to completely eliminate Excel as a component in data processing. Had a workbook that did some very complex manipulations with matrices. I found code on web to replicate the calcs. I discovered that the results within Access varied slightly from the Excel workbook calcs. Even when I used Excel matrix functions within the VBA code the results were different. Which is correct? Discussion with boss determined the variance was acceptable and I abandoned the Excel. Maybe your calculations aren't as complex as matrix multiplication and you won't see any variance but be aware there are differences between the apps. Another for instance, Round function in Access uses even/odd (banker's) rule, Excel does not. I found that out quite by accident. Another discovery is that Format function does not use even/odd. So I use Format instead of Round then when needed convert the formatted result back to a number with Val().
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    MickFlanagen is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Apr 2013
    Posts
    11
    So I've been doing some more reading, and I think I should add some details to my post to help explain the problem a little more clearly.

    For a typical contract, the user would enter a start date, a term, and two rates (both rates apply to each period, for example, $10 for the rental, and $5 for service fees totals to $15 in each term. It is important to keep these separate)

    On top of that, there they may add rate increases on a $ basis or % basis, which may apply to one rate or both. Furthermore, there may be a period (say, the 13th month of a two year contract) where we don't charge them anything at all. There are a couple other complexities (sometimes the second rate is a function of the first, sometimes it isn't), but you get the idea.

    The point of providing all of that is to say this: in excel, I have all of these inputs feeding into a hidden worksheet that lines them all up and gives me a stream of cash flows for each as well as some streams for various totals. I then apply my NPV to this.

    I know you can use excel formulas in Access, but I don't know of a way to line up all of the data like I do in excel. I don't want to store calculated numbers (apparently this is a no-no, which makes a lot of sense to me), but trying to figure out how to write one HUGE NPV formula with all of those complexities makes my head hurt.

    I hope that helps clarify my problem.

    Thanks again,

    Mick

  4. #4
    MickFlanagen is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Apr 2013
    Posts
    11
    Thanks June7, that is good to know. I will definitely try and keep my eye out for calculation differences to see if I am getting the same results.

    Mick

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Don't know what NPV stands for.

    The calcs you describe don't appear to be higher mathematics, just simple arithmetic. What does get complicated is the decision logic that needs to be programmed.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    MickFlanagen is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Apr 2013
    Posts
    11
    Sorry, NPV is Net Present Value. It discounts a series of future cash flows at a set interest rate, almost like a mortgage. For example, 10 monthly payments of $100 is $1,000. However, if you give $772 today, and I can earn 5% interest on that $772, I would have the same $1000 at the end of the 10 months. So, the Net Present Value of $100 for 10 months using a 5% discount rate is $772.

    The calculation in and of itself is very simple. Access even has a function for it. My problem is determining what each monthly payment is. For example:

    A contract starts on January 1st, and lasts 24 months. The basic rate is $10, which goes up by $1/month and the service fee is $5 and goes up by 2% every year. The 3, 6, 9th months are all free, and the client has the option to cancel the contract after 18 months for $100 charge. So, what I need at 24 monthly numbers based on all of those factors.

    This is very simply in excel, but a bit beyond me in access. Should I be asking the user to enter the details into Access as I have above and performing the calculations behind the scenes (via VBA?), or should they enter them into excel as they do now and have each monthly payment calculated in excel and transfer that data into an access table?

    If they just enter the basic information (like I did above), should I store the monthly payments somewhere, or just calculate them every time I need them? I suspect I should calculate them every time I need them, but if I am trying to find a value for 500 contracts, that might take a while and be a messy process.

    Again, I'm sorry if this is a simple question. Maybe I'm just thinking about it in the wrong way.

    I appreciate the feedback so far.

    Mick

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    You might find this of interest http://forums.aspfree.com/microsoft-...el-349267.html

    In Access, the basic data could be input but for Access to do the calcs will involve VBA code to save 24 records for 24 monthly numbers as opposed to calculations in a query to generate 24 calculated fields because not every contract will have the same terms - 24 months, 36 months, etc.

    Access might not be the best tool for these calculations. However, it is the tool to track customers, suppliers, purchases, sales, obligations, payments, etc. So a Contracts table could have terms info, Payments table for tenant remittances, etc.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Access to Excel template
    By thart21 in forum Import/Export Data
    Replies: 1
    Last Post: 02-16-2012, 05:49 PM
  2. Replies: 3
    Last Post: 09-14-2011, 05:27 PM
  3. Reversing Conversion from Access 07 to Access 2000
    By ottawaroderick in forum Access
    Replies: 5
    Last Post: 03-16-2011, 10:33 AM
  4. Conversion in Access table
    By Lucas83 in forum Access
    Replies: 1
    Last Post: 04-06-2010, 11:51 AM
  5. Conversion from Excel to Access
    By TMG in forum Import/Export Data
    Replies: 1
    Last Post: 10-20-2009, 12:48 PM

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