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:
- 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
- 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.
- 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.
- 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