Results 1 to 4 of 4
  1. #1
    tmacg7 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2016

    Is Access the right tool for this?

    Hi -- before I dive in on learning MS Access, I just want to make sure that I'm going to be able to accomplish what I'm looking to do and which I'm struggling to do in Excel. Any thoughts on this would be much appreciated!

    So this is for a professional services firm and the general business model is employee hours X bill rates = revenue. Most employees are salaried. I'm trying to build a model that calculates billing capacity and also salary expense. Both of these are driven by various attributes about individual employees (their standard bill rates, their PTO allowance for the year, what % of their annual working hours should be billable, their monthly salary, their planned salary increases, promotions that change some of the previously mentioned attributes, etc., and a number of effective dates for all these things).

    So when I'm planning for the full year, there are a number of changes that get planned for by employee for specific dates. I want to be able to have salary cost and billing capacity automatically re-calculate as I enter various changes and effective dates of changes. For example, if I have a new hire planned for 4/1/16 and then change it to be for 5/1/16, I want the salary expense and billing capacity for April to be zeroed out. That itself is easy enough to do in Excel (and this is a very simple example), but the hard part comes with the fact that we do monthly re-forecasts and have to be able to explain the changes from one forecast to the next. So I need to save what was planned in one forecast and then add to it (sort of like copying and pasting it all and tagging it with a different forecast name and then making specific changes to it -- or adding new records that add to / subtract from the prior forecast). Additionally, changes need to be tagged with one of about 10 different "reason" categories. So all this ends up being a lot of "records" to store and do calculations on, and there are a lot of dimensions related to all these records / used in the calculations. I probably could build this in Excel / PowerPivot, but it feels like I'm trying to use Excel as a database which I know people frown upon. I'm sure I'll uses Excel / PowerPivot for some of the analysis, but I think what I'm needing to do is to use something else (Access) for holding all the records and maybe for calculating all the monthly amounts.

    A big part I'm trying to solve is how to have monthly records added as needed based on effective dates. In Excel, I have to make 12 spots for data to flow into if something is happening for an employee for every month. But what I want is to not have to add 12 spots "just in case" -- I want the model to just look at the effective dates and create the monthly calculations for the months they are actually needed for. I don't know if that means it automatically adds records to tables based on effective dates, or if it just calculates all these monthly amounts via queries? I hope this question makes sense. Is this something that Access would do better than Excel? I don't know how to make Excel add rows / records to a table based on how many months are between two dates (I would guess it could be done with VBA, but that feels like a long way off for me).

    Thank you for any help in understanding which direction I should go with this.

  2. #2
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Knoxville, Tennessee
    If you already have data stored in excel, you can link those excel files into access as if they were regular tables. And reference them from there. I would start with access. It would be the tool to use IMO to get started.

    Im notorious for mis-using technology that was ment for one thing, but use it for another. A wrench doubles as a hammer most of the time.

    Since you have the base business logic concepts already hammered out, you should be good to go to access.
    Patience serves as a protection against wrongs as clothes do against cold.

  3. #3
    tmacg7 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2016
    Thanks, that's good to hear it sounds like I'm heading in the right direction. And I do have data in Excel already, but I'm fine to just completely re-enter it to whatever extent I need to. Since this has all been done in Excel before, we've limited these models to be for a single year, so no need to go back multiple years. And since it's still early in 2016, we've only done four forecasts. What would be great though would be the ability to just have one ongoing file over the years. That would be far too messy in Excel, but maybe it would be easy enough in Access. Part of the issue with that in Excel is that -- if I think about it in a database type of way -- we have to basically create something like a "record" for each employee for each month for each aspect of how their salary for that month was arrived at, AND for each forecast. So I'm picturing instead a model where I just enter records for "changes" that include the monthly change amount, what the effective date is, and what type of change it is. Then the application figures out what all the monthly amounts are based on that, but it's not actually creating records for each of those monthly amounts. The potential for this is very exciting to me, but also worrisome coming from an Excel perspective where I'm used to being able to just look at the calculated numbers in the data tables. Just something to get used to, I guess?

  4. #4
    ssanfu is online now Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Anchorage, Alaska, USA
    You might want to work through the tutorials at
    Rogers Access Library

    Before you go all out with the computer, design your tables using pencil and paper, chalkboard, whiteboard, sticky notes, etc. Much easier to change table designs and you can "walk through" adding data to see if the table structure is sound.

    If you have questions about your table design, post back with questions.

    Good luck with your project.
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

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

Similar Threads

  1. Access Tool
    By cbende2 in forum Access
    Replies: 10
    Last Post: 06-15-2015, 02:55 PM
  2. Using Access as a Resource Management Tool
    By Datech in forum Access
    Replies: 3
    Last Post: 05-21-2013, 01:17 PM
  3. Is Access the right tool for me?
    By buienxg in forum Access
    Replies: 1
    Last Post: 11-26-2011, 02:32 AM
  4. Is Access too big a tool for simple data?
    By chilly hellion in forum Access
    Replies: 3
    Last Post: 03-01-2011, 07:43 PM
  5. the search tool in access
    By Grooz13 in forum Access
    Replies: 1
    Last Post: 08-31-2010, 09: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 - Senior Forums