An Access Report for Work Items

1. I have two tables that are being exported from an online database in an excel spreadsheet. 2. Each table contains data from a year. 3. The data contained in each table is similar. 4. There are unique item numbers in each table: completion dates for each item number, start dates, labor hours, etc. (date format: mm/dd/yyyy) Example: Item 232323 - Date Created - Date Completed- Assigned to Agent Name- Labor Hours. 5. I need to build a report that can: a. Compare the quantity of items present year to year, quarter to quarter (within years and between years), month to month. b. Apply a numeric index for a range of hours for each item. i. Example, item 2243 had 23 labor hours. I would have a scale where 1-5 gets an index value of 1, 5-10 gets 2, 10- 15 gets 3 etc… ii. Once these indexes are calculated, I need to use this data to have percentage improvements comparisons year to year, quarter to quarter (with and between years), month to month. c. Find turn around times for items. How fast they were completed (diff between date created and execution date) and here’s the catch. There’s another field called date completed. There needs to be a function that uses completion date in the calculation if execution date isn’t there. d. FINALLY: I need to assemble all these numbers (the percentage improvements or decreases for each calculation, and give each calculation a weighted number based on the percentage. I need something like a progress bar at the bottom of the report to display this. e. So need the progress bar, pivot charts to compare values (percentages and quantities), Tables. Etc. Is access the best option or excel to have something this analytical? THE CATCH: the report has to be reproducible, meaning, the end user can throw in next year’s item excel file from the online database and use that to compare last year’s data output. The quantity of items will constantly be increasing. So the xls output files being analyzed will be updated on a regular basis. . ALSO: I need to run multiple filters to weed out outliers in the data as well as specific members assigned to each item. (ex. Brian, bob, sandy, Jill are in the excel output… there are actually far more people in the output file (14000 items)), I would need to input a specific team with specific people (usually 10 people) into the report parameters, and filter those individuals and then run the metrics above. When it comes to outliers, I would want to not include certain data in the calculations for the report. For example, if I'm calculating turn around time, I would want to exclude items that have been worked on for more than 1 year. ALL IN ALL- This isn't too complex for someone who knows their stuff. There's just alot of formulas to put in and some clever design for the report. I'm asking for a performance report essentially. If any teams think they can accomplish the same analysis and task using excel (this includes being able to throw new data in every time we run the report), you are free to bid for that method as well. I need a complete product very soon. THANK YOU. And please let me know if you have additional questions.