Database vs. Spreadsheet

[knight fyre]

I'm not sure where this should go but here goes:

A student of my mom asked me to workup something for her to store informations and do calculations.

Basically she described an events planner where she'll enter a date, name of event, the cost, and the payment to be received. Money owed, totals for the month and for a particular category will be calculated. She should be able to enter US, POUND, and a few other currencies of her choice and see in conversion in the amount owed/paid columns.

I used an Excel spreadsheet to do the job, most of her requirements anyway. :$. I detected a few minor kinks in certain formulas only after she paid for it but these kinks doesn't stop her from doing her main tasks anyway and I'll fix them the next time she comes by.

Now she's asking me to change a few things (which translates to additional complicated formulas on my part).

Should I continue working with the spreadsheet or would an Access Database file work better in this situation? When is a database the better choice vs a spreadsheet and vice versa for a small to medium number of records.

[DangerDev]

nice.......

see if u have to impress some one and if u r good in some programming language use data base and make one application using language. this will give u extendability and with each new requirement u will be able to move forward.

so if u dont know any lang learn some uptill then use spreadsheet.

[MidiMagic]

The main advantage to the database is the ability to use forms and reports.

Both use the same basic kinds of formulas.

[Ancient Dragon]

If you use Access (or any other database) you will have to lean SQL query language and one of the many programming languages that can access the database, such as VB, C#, C and C++. Using Excell you don't have to know anything about any of that. So your choice will depend on how well you know Excell and database languages.

Word of warning: If you elect the database route and write computer programs for it then you should add copyright notices at the top of every source code file. Its important that you retain the copyright of everything you write even if someone pays you to write it.

[leo002000]

I think the job is easily done in Excel. And provides the analytic possibilities and is even more simple than say an average database like access. Although you may find that periodically using access to export data from Excel to a database would provide you with a basic data warehouse of some sort. This may be of interest from an historic data point of view. Obviously Excel 2003 has 65,536 row capability access has about 2GB which is far more. Maybe stick with Excel, and learn Access then miagrate/interlinct the two programs at a later data, with data imports bi-directionally between the two programs. use the microsoft office online tutorials they are very good and easy to follow. Good luck and happy reading.

[knight fyre]

Thanks for clearing that up. I'll remember that when I start writing software commercially. I know enough SQL to do basic tasks but is C++ applicable to MS Access?

C++ is something I've just started learning so writing a complete solution with database interaction is far beyond me at this point but a skill I'm interested in acquiring.

If you use Access (or any other database) you will have to lean SQL query language and one of the many programming languages that can access the database, such as VB, C#, C and C++. Using Excell you don't have to know anything about any of that. So your choice will depend on how well you know Excell and database languages.

Word of warning: If you elect the database route and write computer programs for it then you should add copyright notices at the top of every source code file. Its important that you retain the copyright of everything you write even if someone pays you to write it.

[leo002000]

To be honest with you, I would start looking at 3things. 1=MS Access "gets lots of stick but great to learn database concepts as its visual" 2=SQL server 2005 express edition and related tutorials.3="Visual basic 2005 express edition or 2008" by far the easiest language to learn, its visual again! You would easily learn to export data from excel to access then upsize that database to sql server 2005 using the upsize wizard in access,then design a simple form with data grid in visual basic 2005/2008 express "freeware" and query the database from your application. I bet within a couple of days of really looking at this you would be able todo it. Or you could just simply link the Visual basic application to the MS access database. There are loads of tutorials about.

[knight fyre]

My primary reason for considering Access is not it's data warehousing capabilities but the restrictions its interface offers vs Excel. In Excel, my client can alter the formulas and manipulate the spreadsheet in ways that interfere with it's functionality (unintentionally). Trying to implement the protect sheet option in Excel restricts the capabilities even after tweaking. In Access, he/she is limited to forms and reports I create.

I think the job is easily done in Excel. And provides the analytic possibilities and is even more simple than say an average database like access. Although you may find that periodically using access to export data from Excel to a database would provide you with a basic data warehouse of some sort. This may be of interest from an historic data point of view. Obviously Excel 2003 has 65,536 row capability access has about 2GB which is far more. Maybe stick with Excel, and learn Access then miagrate/interlinct the two programs at a later data, with data imports bi-directionally between the two programs. use the microsoft office online tutorials they are very good and easy to follow. Good luck and happy reading.

:-/ Hmmm, a very interesting suggestion indeed. That's definitely something worth looking into during the summer when I have more time. In your first suggestion, is that a standalone application of would I need sql server 2005 along with other software to run it? Remember that I'd be selling my product to clients and they will not have all these software packages installed.

To be honest with you, I would start looking at 3things. 1=MS Access "gets lots of stick but great to learn database concepts as its visual" 2=SQL server 2005 express edition and related tutorials.3="Visual basic 2005 express edition or 2008" by far the easiest language to learn, its visual again! You would easily learn to export data from excel to access then upsize that database to sql server 2005 using the upsize wizard in access,then design a simple form with data grid in visual basic 2005/2008 express "freeware" and query the database from your application. I bet within a couple of days of really looking at this you would be able todo it. Or you could just simply link the Visual basic application to the MS access database. There are loads of tutorials about.

[leo002000]

Hi,

Its easy to create "Read only" excel work books/sheets, hide all worksheets that aren't needed for the user, i.e. where some of the math/manipulation is occurring "the underlying logic/data component of the workbook., have a standard sheet which the user always has to go to on opening "macro based" at workbook launch. From there I would put some buttons in which would enable the user to navigate to specific worksheets/areas of interest or graphs or such. I tend to use the "autoshapes" like the cube or something and simply "assign a macro" to it, this way you get a good looking button with "fill effects" if you like, which creates a nice looking interface. I would hide the "gridlines" and "freeze panes" to allow a certain area to be viewable. This you could call the "start up screen". You can basically do anything and everything with excel.

As far as your self built application and linking/querying to a database goes, maybe a good starting point would be Microsoft Access "not promoting it" but certainly the easiest to use. Create an application using for instance "Visual Basic 2005/2008 express edition" "check legalities as far as selling your products are concerned" and you could lend bind a data grid in VB 2005/2008 to a table or such like in an access database on the same pc or over a network.
You could allow for read/write so that a user can add data and even remove rows etc.
This although isn’t that complex, will take some reading and learning of new packages and of course VB programming, although it can be done, I would suggest that if you are serious about developing databases and associated applications, to start the long a hard road of learning about "SQL server 2005 express, Visual Basic 2005 express, Visual web design 2005 express, Access 2003>, Oracle 10g Xe, ADO+ADO.NET, the ".NET frameworks" etc. Of course you could create easier databases using Access, with simple forms etc and by hiding the database window, would stop the customer from altering the database/tables themselves, look into Access database security. Most of all keep learning.

"All of the packages I mention above are not endorsed by me, in any way shape or form, I just happen to know a fraction about them, hence I use them to reference. There are far more people on here and on other places who will know lots more about these and other very capable packages from differing software vendors".

Best Wishes
Leo