I endorse all of the advice that Al has given you as far as whether
you should be undertaking this as an Access project. Since you're
already posting here it seems that you have an interest. If that's
true, press on. Ultimately, Access is designed for applications like
that whereas Excel was designed for calculation intensive
applications. Excel was here first and for each generation of Office
tends to be the most complete and the most sophisticated. Excel is
the lingua franca of the Engineering world and most of the financial
world because it allows "roll your own" applications from a very
simple level.
Now, before you dig into your Access project, I recommend that you
automate your Excel process to another in one or more ways. The first
way is to provide an Excel template to each of the people currently
entering data into the individual Excel spreadsheets. Make it a
requirement that they place their data in a copy of the template that
you provide. That template must contain all of the required
information, column headings and formulas. Your clients forward the
current spreadsheet to you at the end of the reporting period. If it
is required that each account have its number within the spreadsheet,
make sure that people enter it in the same place each time (actually,
they can enter it in the right place in their master template and them
copy it each reporting period). You could also create a data form for
them or give them instructions in how to do it. It would be a good
touch to have a date field that defaults to the current system date.
That form gives them a more elegant way of entering data than mucking
about in the cells.
Next, there should be an agreed structure of folders on your server
where you can trust that everyone's spreadsheet will reside.
You can now design a higher level spreadsheet that will read the data
from the individual sheets and place it properly. You'll have all of
your formulas in it properly. You will have one copy as your master
template and make a new copy for each reporting period. You should be
able to generate any required reports. If you need to run scenarios
on the current, or past, data, take a copy of the master sheet and do
it there.
Once you've done all of that you'll have a good handle on your current
work. Devote the time you now save on the Excel work to learning
Access and designing and debugging your Access application that will
replace the Excel application.
The application you're looking to create in Access is considered to be
very simple. The difficulty is in getting enough Access proficiency
to do the job. The learning curve in Access is at least an order of
magnitude greater than that of Excel as far as getting from ground
zero to journeyman level proficiency. Once you get there you can do
some awesome things.
HTH
