Is Access an Adequate Tool for This?

[Tony Tombola]

Hi guys!

I'm currently working on the set of data stored in xls sheets. These data
are organized as follows:
There are several (top-)folders, each containing the data for the whole year
(2002, 2003, 2004).
In every year-folder, there is 12 subfolders (for every month: 01, 02, 03,
04, ...)
In every month-subfolder, there are 15 excel files, every file containing
the data for separate country (f. ex. "10-2004 Germany.xls", "10-2004
France.xls" etc...)
Every country file is about 2000 Kb and contains the data partially
introduced manually by people working with these countries during the month,
and partially retrieved from accounting and workflow software. Each workbook
contains like 10 worksheets within. The structure of every file is
essentially the same, however there are some minor changes between today
and, f. ex., one year ago.

There is one lady that compiles these sheets once per month (this takes her
2 days I guess (not kidding!)). These files are then used to prepare reports
and statistics on the activity per country.

So, I do not need to be a genious to find out that such organisation of
data/statistics is time-consuming, very rigid and very error prone. Morover,
inside these workbooks (and also between the workbooks) the cells/tabs are
interrelated (formulas between worksheets), which renders the whole system
very opaque.

So, I would like to make here some radical rationalisation and eliminate
this "clock-bomb" that will probably explode one day. However, as my MS
Access knowledge is very limited (essentially, I know its name and the fact
that this is the database software), I'm asking myself if this application
would fit good for the task of securing the above-mentionned data, for
making some calculations between these data and finally for producing some
statistics and reports. If MS Access is a good tool for such tasks, I'm
ready to make some investment and to learn how to handle MS Access.

So, essentially I would be very obliged if you could let me know your
thoughts on this issue.
Is it worth moving from "xls file"-system to the db-system?
Is the MS Access an appropriate tool for dealing with such data as
mentionned above?
Are there any other alternatives that you would use at my place?

Once again, I thank you for all your help and comments.

Tony

 

[tina]

Access is an excellent tool for the data sets you describe. to get the full
benefit of the power of the software, you'll have to invest a substantial
amount of time in learning to build a database correctly (learning that is
*not* the same as learning the mechanics of using the software). but once
you've learned, set up the proper tables and relationships to hold your
data, and begun to learn how to create queries - you'll be amazed at how
easy it is to enter data on an ongoing basis, and how easy it is to
extrapolate virtually any information you want for analysis and reports,
both current and historical. what used to take two days every month may take
only a few hours.

suggest you focus on a couple things: 1) don't "commit spreadsheet". Access
tables look similar to Excel worksheets, but relational databases work very,
very differently. you need to understand how a relational database works, so
you can leverage its' power. 2) learn about table
normalization/relationships *first*. the rules of proper table normalization
are the same regardless of the software you use - whether Access, Oracle,
SQL Server, etc.

one good book on database modeling is Database Design for Mere Mortals by
Michael Hernandez, which has been recommended many times in these
newsgroups. once you have a solid understanding of database design, a good
book to learn how to use the software is Microsoft Access <version> Bible by
Prague and Irwin, which is also often recommended.
before you begin building your database, i also recommend you read the info
at the following webpages:
http://www.mvps.org/access/tencommandments.htm
paying particular attention, at this point, to numbers 1, 2, 3, 7 and 10.
http://www.mvps.org/access/lookupfields.htm

the Access Web website is an excellent resource to bookmark, and these
newsgroups are also an excellent ongoing source of help, advice and
suggestions from many experienced users including some of the top developers
in the world.

good luck with your learning adventure, and have fun!

hth

 

[Bruce]

In addition to Tina's reply, I offer the following. Unlike with your
spreadsheets, you will not have a table for each year or anything like that.
You will just have dates, and Access can be made to sort or group them as you
choose.
Access works through relationships between tables. Without knowing the
details it is defficult to come up with relevant suggestions, so let me use
the well-worn example of an order tracking database. In that case you have a
customer table and an orders table. Each customer may have many orders, but
each order is associated with only one customer. By establishing a
relationship between the two tables it is possible to call up the customer
name and look at every order in the last year or whatever. You don't need
the customer name in every order record; all you need is a link to the
customer in every record. A properly set up database does that without your
intervention. Similarly, each order can have many line items (product,
quantity, price, etc.), but all line items for all orders for all customers
are stored in a single table. The main thing with a relational database is
that you don't store the same information over and over.
There is a learning curve, but it is well worth it. Excel is a fine
program, but you are pushing its limits in your current system. You're going
to really like the power of relational databases.

 

[PC Datasheet]

Tony,

Please email me at my email address below.

 

[AdmSteck]

I would just like to relay my experience thus far because I was in the same
boat. Where I work, Excel is the bread and butter for just about all
purposes. It wasn't until recently that we started to convert a few key
areas to Access. The time savings that we are just starting to see and the
ease of use has been more than enough to justify the learning curve involved.
If you do start using Access, another good first step should be to take an
overall look at your organization and see what other areas could benifit from
Access. This is one thing I wish I had the foresight to do in the begining.
Now I am rebuilding some of the first databases that I created to link to
others and commonize things a bit. I hope your experience turns out as well
as mine and good luck.

 

[Tony Tombola]

Hi Tina, Bruce and AdmSteck,
Receive my excuses for the delay in reacting to your input!

I just wanted to thank you for all your insightful comments.
With all the valuable information you supplied it's now my turn to convert
it into something "real".
Thanks once again!

Have a good day,
Tony