Results 1 to 4 of 4
  1. #1
    Datech is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    40

    Question Using Access as a Resource Management Tool

    Hello everyone,



    I'm new to Access, and obviously new to this forum. I've got a new task at work to create a small but robust resource management tool in Access to replace a burgeoning Excel spreadsheet. We've already got a good database design in place, but the biggest issue is figuring out how to configure the reports to display what we want.

    I'm under no delusions that y'all can solve my problem in one post, and I thoroughly intend to learn as much about this process as I can. Ask me any relevant questions or direct me to any literature that you think I need to read to get up to speed. Also, feel free to move or reclassify this thread if it's in the wrong place. I have Access 2010: The Missing Manual, but I haven't had the time to even crack the cover yet.


    So, brief description of the database and purpose:
    • Table for all employee data, with fields like Manager, Role, and Employee Class
    • Table for all projects, including the overall Account, the project manager, and internal billing code
    • Table for what employees are on what projects (Resource Allocation), that ties together an employee with a project, and specifies the funding source. Most importantly this table tracks the start and end date of an employee's deployment on a project, as well as the percent of the employee's time on the project for those dates.
    • We set it up this way because that's how the Excel sheet tracked it, but also because an employee can be working multiple projects at the same time and with varying levels of utilization. So far this method tracks the information we want effectively, but as I said the biggest problem is displaying it effectively.



    The problem:
    I don't know where to start in creating the reports. Here is a brief description of the reports we want:
    1. Something like a Gantt chart that shows a visual representation of the actual start and projected end dates of each project across a timeline. This would be directly related to the dates taken from the Resource Allocation table
    2. A headcount display of employees by project, both by month and by week. We used Pivot Tables in Excel to show a Count Of how many employees were on certain projects in a given week. If employees were on multiple projects we would designate which project counted as headcount, since an employee would only need to be counted on one.
    3. A utilization display of employees by project, both by month and week. This is similar to the headcount, but here we want to know the total utilization of all employees on a project, so employees on multiple projects would be displayed on all the projects, not just the one that counted for headcount.
    4. An availability display. Basically if an employee isn't being 100% utilized, we want to know who, when, and how much extra they can work.



    A few considerations and admissions.

    • This is far and away the job of a SaaS, or packaged resource manager (EPM Live comes to mind).
    • Most of my reporting issues and database functionality center around how Access understands, calculates, and displays dates.
    • The reporting issues also concern how Access can graphically display or tabulate such date information in both week and month granularity.
    • The entire responsibility for this database falls on me and another co-op. We're both students who are working part time, and have little to know Access or database experience.
    • Right now this database will only track ~120 people. If it is implemented correctly, management wants it to track ~500.



    This is a lot of information and a lot of requests. Feel free to start with the easiest solution to a simple, standalone task, and let me know how I need to approach it and what extra knowledge I need. If you need more information, or samples of what I have so far, let me know and I'll work on providing that. And again, this is my first time asking for such help, so let me know if I'm way out of bounds or off base.

    Thanks so much!
    Daniel VanBeek - Datech

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Your basic table structure sounds okay. Review http://forums.aspfree.com/microsoft-...es-208217.html
    However, if the database really is limited to 120 people, something must be wrong.

    For Gantt report ideas, check this thread https://www.accessforums.net/showthr...ighlight=Gantt

    All of the data manipulation you want to do can probably be accomplished with combinations of queries and code. I only use VBA not macros. Reveiw http://office.microsoft.com/en-us/ac...010341717.aspx

    Get familiar with intrinsic functions, review: http://www.techonthenet.com/access/functions/

    Find time to crack that book cover.

    As you encounter issues and have specific question, post in a thread and we will try to help.
    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
    Datech is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    40
    I don't have a problem with how many people it handles, I simply don't want to do the data entry and get the weekly reports for that many people! lol

    I'll check over everything you provided me and I'll let y'all know when I have questions. The biggest issue facing me is physical evidence of the visual reports my boss wants. If I can show him he'll get what he wants I'll probably be free to do everything I want with this for another week.

    Again, thanks so much!

  4. #4
    amistry is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Posts
    1

    Any updates?

    Hi, just wondering if you've made any progress here?

    I'm trying to do something similar; transitioning excel to access.

    Thanks,
    A

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

Similar Threads

  1. Is Access the right tool for me?
    By buienxg in forum Access
    Replies: 1
    Last Post: 11-26-2011, 02:32 AM
  2. 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
  3. Replies: 3
    Last Post: 10-11-2010, 10:23 PM
  4. the search tool in access
    By Grooz13 in forum Access
    Replies: 1
    Last Post: 08-31-2010, 09:29 AM
  5. Access to Excel: Financial Forecasting Tool
    By BLUE_CHIP in forum Import/Export Data
    Replies: 2
    Last Post: 06-19-2010, 01:52 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