I want a MS Access DB built for 3000 department users to upload 3000 excel files onto an access table once a month. The excel file is a template that I want to create and will have 7 columns to contain customer data for each department with varying number of rows for each month. At the end of the month, I will have all 3000 files in one access table to analyse. The 7th column in the excel file is free format text where users input a reason as to why the record exists. MS Access has to accommodate for it. The rest of the columns will be consistent and formatted data.
I am expecting this final table to be fairly big even though I hope to hold the data for a rolling 12 month period only.
First and foremost, my users are not very clever and not MS Access savvy. So this upload screen has to be simple, aesthetically pleasing to use and also controlled and protected so that they can't break it or corrupt it. (Get into MS Access, one or two button clicks and the data must be loaded and get out)
Then we need to facilitate this upload in such a way that if there are errors in the data when loading (Ex: wrong date format in the file) then we give an error message and not commit the data to the main table until we have clean data to upload.
We also need to create a simple reference table to hold the description of one of the columns in the main data table and join later (1:M join?) when we need to report.
That is all the data entry done. Now for the analysis:
As I collect this data on a monthly basis I need to be able to analyse the data into a standard set of reports. Say 5 reports max as follows:
1) Compare current month's data to last month's data and report the matching records for each department (You can think of this data as customers who didn't make a payment on time. If a payment is not done in the last two months we need to report that customer. If there is a payment this month but if that payment was not for the last two months then we still need to report them because we are reporting on the last two consecutive months that a payment was missed. So obviously, one of the data columns is the payment date)
2) Provide a monthly trend analysis of the volume of records being uploaded into each department (user will pick a department from a drop down list or type in their department number and it should display a graphical chart of volumes by month may be?)
3) Report the Top 10 departments by volume of records being loaded over a period of time (say the last 3 months)
4) Which departments have the most persistent entries? (customers that appears most over a period of time amongst all the departments)
5) Any other report that I might decide will add value based on the data after developing this tool. When I demonstrate this to the users they will look at it and suggest another report. Therefore, the 5th report has to be built after the completion of the project.
I don't envisage this to be a daunting project for someone with in depth MS Access skills as most of this is basic. The challenge lies not in building a tool to do this, but in teaching me how to do it myself so that I can build it in my office environment using O365.
I will provide dummy data and scenarios to reflect production data.
As for me: I am a heavy user of MS Excel and used MS SQL in a previous life. So I am not a complete novice when it comes to understanding logic. I can see a formula and understand what the syntax is trying to do.
But I have not used or built anything on MS Access ever in my life and need to now!
So, this is how I see this working. A MS Access expert will build this as per the above requirements and then teach me step at a time how to build it myself. I don't need to be spoon fed, I am smart enough to understand a process with one or two attempts..
