Database vs Spreadsheet

[Andrew]

I know that using spreadsheets to do what a database is supposed to do is wrong (or inefficient).

From other thread I was going to put:

I really need to learn how to make simple databases. I hear it's better than using excel to store customer data (40 customers, usually 10-15 inputs per customer and about 10 fields/columns). :P

But can they do calculations and math problems? (profit margins etc?)

One of my excel tabs has 47 rows (clients) and 49 columns (data input for each client) to sum up each clients info/calculations for one year.

I've had two friends say I am trying to make a database in excel. But for me excel is easy to understand and manipulate.

OK. So my current 2006 year excel file is 1.46 mb in size. That's not including the data for each time I cut a lawn or any info that I would normally input over the season. (the rest is formulas and calculations etc which takes the info from each customer and expenses data input etc and spits out thousands of calculations).

I hear people say to use databases. I only have a limited amount of stuff I use, I don't see why I should use it (or learn how to make/use/edit the databases when I know excel).

I simply create a new spreadsheet every year (actually just copy the old one and delete the data). I do have some spreadsheets that take data from every year (each spreadsheet) to make calculations on growth etc.

I had a friend at one point offer to make me a database (he worked with them some at an office), but I did not take up that offer as I don't know much about databases or how they could help me.

I know for certain situations where lots of data is input and stored that databases are essential. But I would say that I have around 40,000 cells used in each recent spreadsheet (1 year) with data or calculations. I guess a database would be better for crossreferencing data over the years, but I am not sure about the formulas and such that I use would work in database (I don't know anything about databases :P).

An example of one of the formulas I use in from short to long form turns out to be:

Profit per hour = (estimatedprofit/#ofcuts) / (total time to do a single cut)

Profit per hour = (Price*#ofcuts - TVC - Part cost) / (total time to do a single cut)

Profit per hour = (Price*#ofcuts - TVC - [4 job times added together / 4 total job times added together*part cost) / #of cuts for that lawn]) / (total time to do a single cut)

Profit per hour = (Price*#ofcuts - [[total ride on time*cost of gas per hour + total travel time*cost of gas per hour + total bag time*cost of gas per hour + total trim time*trim cost per hour] - (4 job times added together / 4 total job times added together*part cost)] / #of cuts for that lawn)] / (total time to do a single cut)

Profit per hour = (Price*#ofcuts - [[total ride on time*(total cost of gas per year so far /(total ride on time+Total trim time)  + total travel time*(total cost of gas per year so far /(total ride on time+Total trim time))  + total bag time*(total cost of gas per year so far /(total ride on time+Total trim time)) + total trim time*(cost to fill trimmer)] - (4 job timesadded together / 4 total job times added together*part cost)] / #of cuts for that lawn)] / (total time to do a single cut)

So that one formula ends of using lots of information just to calculate profit per hour for a specific lawn. That's why I have columns to calculate each variable, so that it is easier to see the output of each variable and to easily calculate each profit per hour by copy/pasting the formula. (I probably screwed up showing the formula there, but you get the idea that it uses lots of variables)

And so every time I enter in that I cut someones lawn it recalculates around 2000 formulas (to reflect the newly added data).

I'd also have this question for the farm, as I currently have field data in spreadsheets and so far it is ugly, but I have not spent enough time on the spreadsheets. (We have professional software for accounting, so I'm not really looking for help with that, even though the professional sofwtare is missing lots of capabilities, such as maybe ~ 10 financial formulas)

What are your opinions on using database vs spreadsheet for business purposes? Is it easy to create/edit/use/input data/get reports?

[Gobalopper]

You can still use Excel. Just pull the information out of the database to do your reports.

[Andrew]

Err, how do I use excel and pull information out of a database for reports?

Or are you saying that databases can create reports based on data in spreadsheets (or databases)?

Or are you saying I can easily convert my spreadsheets to databases (by importing them to access or whatever), while still inputting the info into excel (but using databases to create reports?).

Confused. :(

[Gobalopper]

Well if its a simple database you can use Access to store the data and then extract the data into Excel when you want to run your reports.

Granted I've never done that before but it should be possible. :)

But if you don't really need a database for the data you have then I wouldn't worry about using one. Like if you only have one "table", in the database sense, then it doesn't make much sense to use a database to store that.

[gryphon]

You can do anyting with a database you can with Excel. And more.

(or use Excel as front end for yoru database)

The downside for you I recon is that a database requires a computer you can always access. Not a file you can carry around where ever you go.

To safe you from a lot of technical babling. I prefer a database. Although in your case why bother. You need to work with it and if you feel comfortable this way don't change because yo think you have to.

[Cyborg]

The access thing applies just as much to Excel files as it does to databases.

If put on a remote location, that location must be accessible at all times.

If kept locally, it's always there for you.

[Andrew]

Ok. :P

I'll be using spreadsheets still. Simpler to use.

I'll be sure to ask questions about formulas and arguments when I can't figure them out.

[gryphon]

The access thing applies just as much to Excel files as it does to databases.

If put on a remote location, that location must be accessible at all times.

If kept locally, it's always there for you.

You can put an Excel file on a USB stick ;) Which was what I was trying to say. :)

[Andrew]

You can put an Excel file on a USB stick ;) Which was what I was trying to say. :)

Ohh, Good point!

Although you can also put the database thingy on a usb stick I would assume. But it would be a lot harder to use on other computers that may not have the same database software (microsoft acess for example, whereas spreadsheet is compatable with most software)

I did put my spreadsheet on a HP IPAQ Pocket PC, although the latest versions of the spreadsheet run extremely slow on it (because auto calculating tens of thousands of cells is probably hard on the cpu).

[gryphon]

you can indead also put the database on a USB stick. Differance, a database needs a server. Which needs to be installed on the computer you put your USB stick in. An Excel file just need Office installed on the PC and no database server and client tools. :)

[Andrew]

A server? Ew. Now that just scares me away. I thought you could just opne it with access and input data and edit it and save it, then later on open it again and add data?

Excel doesn't even need office installed to view/edit the spreadsheet. I've opened it with openoffice and I didn't notice any differences/flaws.

[erjin999]

you can indead also put the database on a USB stick. Differance, a database needs a server. Which needs to be installed on the computer you put your USB stick in. An Excel file just need Office installed on the PC and no database server and client tools. :)

You are obviosly not taking Access into account, or you simply dont care for it ?

[Cyborg]

you can indead also put the database on a USB stick. Differance, a database needs a server. Which needs to be installed on the computer you put your USB stick in. An Excel file just need Office installed on the PC and no database server and client tools. :)

Excel files need Excel as interface.

Access databases need Access as interface.

I don't see the big difference in functionality.

However, I think you mean more advanced databases like mySQL, postgreSQL and such.:P

[gryphon]

Excel doesn't even need office installed to view/edit the spreadsheet. I've opened it with openoffice and I didn't notice any differences/flaws.

Just ment an office application in general, either Open Office, MS Office, Start Office or Lotus. :)

To Cyborg and Re-erjin99.

I did ment a "real" database like Oracle, MySQL or MSSQL. ;)

You can just just Access and a simple file to store the data. Although in the long run that is asking for trouble. If you need an application "big enough" for a database. Do it good and don't use an local database file.

It might seems like a limited differane. Anthough if you know the differances between a file database (Access) and a real database you never even want to use the file option.

It save you from a lot of problems to start with a real database from the start. If you start with a file way, you gon'na regret if when shifting all the data to a database. (unless you really know Oracle or MSSQL).

I don't see the big difference in functionality.

Use a MSSQL server next to a Access file for a few days and you never wonder which is better for the rest of your life. ;)

I ones had a real life chat with a Google admin from the UK. Ones we got to which database to use .. even he admitted that MSSQL is one of the best. Only reason Google not using it. . . Think costs, think freedom of code, think idealism. . think marketing. And keep in minds that function and quality never made it in the equation. ;)

[erjin999]

No doubt the better databases are better, because they are meant to be better...no one would argue Access is better or more capable then SQLServer or Postgres or whatever.

But are you really saying you had that in mind for Andrew, who uses an excel spreadsheet now ? You are really considering one of the big databases for him and then deciding Excel is ok for him ?  :-

for me it wouldnt enter in to the equation as its overkill.

[gryphon]

to answer that hounestly.

I have no clue why any one whould want to use a local database (file). If you need the abileties of a database. You don't need them locally.

[erjin999]

Because of the time taken to learn between the relative simple access and the relatively non-simple larger DBs ? (SQLServer)

Because Access comes as part of a version of the popular Office ?

Because it is cheaper to buy ? (thinking of SQLServer)

[gryphon]

Nice words. Although I think you should use an application or language for what it can do. Or what you can do with it. Not because some nice marketing terms.

Because of the time taken to learn between the relative simple access and the relatively non-simple larger DBs ? (SQLServer)

Not looking at the time you need to learn it, what do you know after you learned the new stuff. DOS is easy to learn next to WindowsNT. No one will claim DOS is better then the current WinNT command line.

Because Access comes as part of a version of the popular Office ?

Price may never be a reason to choose for a certain software / language. Not the main reason anyway. You have both Access and Excel with Office. So no go here. Why choose one above the other.

Because it is cheaper to buy ? (thinking of SQLServer)

See comment above.

If you want a database instead of a Excel file. Look at the number of users access it. The amount of data in it and the time / power you need for each calculation / querry. You should be supprised how fast Excel fails with 3 simultanious users for the same data.

[Cyborg]

I work a lot with databases, and I can tell you that an Access database is much better than gryphon says. You need a lot of posts to make it go slow.

The database functionality is the same if you use somewhat simple SQL queries(which you probably will).

So if you want a local database file, Access is quite good.

If you don't care if it's local or not, you should consider postgreSQL.

I use mySQL at work myself, but it's not free anymore, as far as I know, and postgreSQL is both more advanced and it's free.

[gryphon]

mySQL is still free. Although they have a more advanced enterprise  version which you have to pay for.

Acces might be great and equivalent to an SQL server. But what's the point as it can't be shared among users in a normal way ? Meaning open file for just one person.

(mind that the shared worksapce, RDP and custom server programms to turn a Access file ar just a work around, it still is open for one user at a single time)

One of the best accounting programs uses a Access file and some custom program. Kuch *Raet* kuch*. And I know it works. Although for it to be *good* software it lacks a lot of things. Things an end-user won't notice or think are important. But anyone who maintaince them for 10+ users who all have other rights and access levels to the data, not to mention backup ways and upgrading the programm that uses the data is bound by.

A few examples.

- user right and ACL's on the database

- How many users can access the database at the same time  (for Access it's still one)

- Just local or remote

- programs to connect to the database

- How to update the DB program

You'd be supprised on the amount of large companies who use a custom program and an Access database for their accounting or inventory. Which is implemente always utterly wrong. Mayby it's just my bad experiance that way I don't like Access databases. :-[ They just provide to much hassle with many users involved with different rights and locations. So sorry for being biased about that a bit. :-[

[erjin999]

Thanks for the quote Gryp :P Especially the first one !

I think you missing the point of the thread, its Andrew we are talking about, its supposed to be a practical database for him to use in place of Excel, as he thinks it might be the right thing to do. The advise seems to be good around the middle of the thread, about looking at what you need doing and if the current setup is working and delivering...as he is maintaining it and seems happy with it, I also believe he should stick with it.

Now talking about using a real DB like SQLServer is way out of the question in that context. That's how I meant my post. I think maybe you think I am putting Access above its place, as in it is better then it should be.

Basically, something like SQLServer would no way be as practical for Andrew as Access would, but if he is happy with Excel and its performace, he should just stick with it.

DOS is easy to learn next to WindowsNT. No one will claim DOS is better then the current WinNT command line.

Certainly I would claim it is better, if it does exactly what I need to do with it and it is quicker to do what I need, and is easier to maintain, and is cheaper to implement and is easy to pass around...and also if it is quicker to learn...I dont know why you exclude learning time. It would definately apply to Andrew as one of the criteria for which app handles his data.

I'll be using spreadsheets still. Simpler to use.

I'll be sure to ask questions about formulas and arguments when I can't figure them out.

A server? Ew. Now that just scares me away. I thought you could just opne it with access and input data and edit it and save it, then later on open it again and add data?

Excel doesn't even need office installed to view/edit the spreadsheet. I've opened it with openoffice and I didn't notice any differences/flaws.

The end user here, Andrew, needing simplicity, the ability to maintain the DB himself and therefor the learning curve cannot be too steep.

Nice words. Although I think you should use an application or language for what it can do. Or what you can do with it. Not because some nice marketing terms.

Those were my words, I should add...maybe I should quit and go work in marketing ;)

By the way, are you talking about Sage ?

[Andrew]

Nah, I figured out that a spreadsheet would be by far best to use for my case.

So continue with whatever it is you guys are talking about. :)

[gryphon]

What I ment to say.

I have no clue why any one whould want to use a local database (file). If you need the abileties of a database. You don't need them locally.

Yes he could have used a local database. If you need a database for the advantages it has above an Excel file. Those advatages you need the database for are almost never just on your computer, just for one user or with easy to implement rights. 3 things a local file lacks over a server.

So yes you could use a database file. But as you need those powerfull options of a database a local database will not be logical as of the reason to use a databse to begin with. You be usign an overkill way to use a database file just for your own needs.

So in my opinion. If you ask to use a database. You should be using a real database. Or non at all. :)

[Andrew]

A new question relating to Excel spreadsheets.

I made an example of what my problem is. The problem occurs on the far right of the spreadsheet with the !VALUE# numbers. I do not want the !VALUE# showing up, as it looks ugly and it screws up the calculation on the top of those errors  =SUM(AF8:AF23)/COUNT(AF8:AF23)  although I tried countif instead of count but same error.

The "" should make it appear blank, but I think since it is getting info from another returned "", it gives the error.

I'm trying to figure out the time between each date (then give the average at top).

I thought I found a solution before to bypass this type of error, but I forget, there are so many numbers/formulas in the spreadsheet.

The example spreadsheet can be found

http://www.mytempdir.com/802166

EDIT:

It can be fixed with =IF(N(U9)<25,"",U9-U8)

[erjin999]

So you have figured it out then ?  :P