If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Is Access an Adequate Tool for This?
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 |
#2
|
|||
|
|||
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 "Tony Tombola" wrote in message ... 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 |
#3
|
|||
|
|||
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. "Tony Tombola" wrote: 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 |
#4
|
|||
|
|||
Tony,
Please email me at my email address below. -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications www.pcdatasheet.com "Tony Tombola" wrote in message ... 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 |
#5
|
|||
|
|||
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" wrote: 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 |
#6
|
|||
|
|||
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 "AdmSteck" wrote in message ... 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" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Conversion Problems | Katherine R | New Users | 11 | December 19th, 2004 12:38 AM |
Access XP Compared to Access 2003 | Mardene Leahu | New Users | 1 | October 1st, 2004 05:11 AM |
Adding staff photographs to my database | KK | New Users | 2 | September 3rd, 2004 07:41 AM |
Error while running Access MDE | Hemil | General Discussion | 2 | June 21st, 2004 01:03 PM |