Access vs Excel as a DB

The point on Excel is that in 1 Column/Row you can differently Format the data(cells). For example - format as Number or Hour the cells in Column B depending on the data in other columns. That saves you from making 2 Columns - one for Numbers and another one for Hours. This helps you to save 1 of the columns when the data structure in other column is the same.

In Access (and generally all SQL DBs) this is not possible

Click to expand...

What you are describing is the inherent nature of how relational databases work. Databases are set up with distinctive records and fields, where a field has to follow the given format it has been assigned. You generally have repeating records of the same fields. While this may seem limiting, it is actually very powerful and allows you to run queries on your data while maintaining the integrity of your data, database, and results.

Excel is a spreadsheet program, not really a database, so there is no "implied" formatting as you move down a column, as there is no inherent relationship between different rows of data, just what you set it up to be.

So Excel and Access often "overlap" in what they can do, they actually serve different purposes, and the key is recognizing which one serves your current purpose better.

By the way, regarding your initial comment/example, as long as you aren't trying to do then use the result of your number/time field in another calculation, you may be able to get what you want in Access by formatting the field as a String, and then using the IIF and FORMAT functions on your calculation to return the value how you want it displayed.

Exactly, what is described is a weakness of Excel as a database IMO.

Try importing a CSV file with different data types into Excel and see what happens. Moral, don't try and save space/columns/formats in such a way, it is not worth it in the longerterm.

Try importing a CSV file with different data types into Excel and see what happens. Moral, don't try and save space/columns/formats in such a way, it is not worth it in the longerterm.

Click to expand...

Excel and Access both seem to have problems when they try to "guess" or "assume" how data being imported is formatted.

Typically, when importing CSV data into Excel, instead of letting Excel try to figure it out (which is does poorly), I always use Data | Get External Data | Import Text File. This invokes the Import Wizard where I can at least tell Excel how each field should be formatted.

Likewise, if you have ever tried to import Excel data into Access, it also has problems (especially if you have some null fields). What's worse, is Access usually doesn't tell you what the problem is. It just says there is an error trying to import the file and aborts the import process. On more than one occassion, I have converted my Excel data to a text file so I can import it into Access.

In general, I find that Microsoft products have trouble when they have to "think too much" or "guess what you are trying to do". It is for this reason that I despise Microsoft Word and their "Autoformatting".

My point was not that Access is better than Excel (in this or any other point, it doesn't hold a candle to Excel), but that trying to cram more than one information type into a column is bad practice - period.

My point was not that Access is better than Excel (in this or any other point, it doesn't hold a candle to Excel), but that trying to cram more than one information type into a column is bad practice - period.

Click to expand...

Agreed. Sorry to go off on a tangent. I was just going off on one of my pet peeves with Microsoft products -- that their attempts to make their programs "more user friendly" for non-technical people often have adverse affects and cause unnecessary frustration for us programmers.

xld said:

My point was not that Access is better than Excel (in this or any other point, it doesn't hold a candle to Excel), but that trying to cram more than one information type into a column is bad practice - period.

Click to expand...

I also agree about the bad practice (and as Joe mentioned earlier you can get what you want from Access using queries).

But I'm curious. In what context do you say 'Access doesn't hold a candle to Excel'? I default to Access over Excel for db jobs almost all of the time. But if it is a traditional spreadsheet task I am presented with, then Excel is the right tool for that job.

On what basis do you make this claim?

Andrew

Like Andrew, I was agreeing with the statement about mutliple data types in one column. Like Andrew, I also do disagree with the statement that Access does not hold a candle to Excel (I should have read everything a little more carefully, I totally overlooked that statement).

I was quite proficient in Excel and VBA before I ever started dabbling in Access. Access was the great "unknown" and I was a little intimidated by it, so I avoided it whenever I could. However, once I learned it and became comfortable with it, it is know usually my program of choice, and I use it much more than Excel now. I find it much more powerful than Excel, especially when dealing with relational data.

So whenever I see someone making disparaging remarks about Access, I know from experience that most likely the problem is that they are just not too proficient in it and not comfortable using it.

So, I can get out these conclusions:

1. Access can do everything Excel can do
2. More powerful, real DB, just for professionals
3. Excel is the user-friendly conception for Access

These 3 points include working with data - add/edit/delete/analyze & working with userforms(controls) - VBA.

The Charts, PivotTables and other little analyze tools are things which we're not talking about here. You can always import a table from Access (through txt file if you want) in Excel and do a chart or analyze with Excel tools additionally if it's user-friendly with the stuff you employ or people you work.

Am I close to find the "hot water"

1. Access can do everything Excel can do
2. More powerful, real DB, just for professionals
3. Excel is the user-friendly conception for Access

Click to expand...

I think you are trying to oversimplify things and misunderstanding what we are saying. Access and Excel are two totally different concepts, they just have a lot of overlapping as far as what you can accomplish with each.

There are things which Excel can do which Access can't, and there are things that Access can do that Excel can't. There are things that both Excel and Access can do, but Excel does better, and there are things which both can do but Access does better.

Quite simply, Excel is a spreadsheet program, Access is a relational database. With some effort, you can manipulate Excel to work as a database. And you can manipulate Access to perform spreadsheet tasks (i.e. calculations, etc.).

Also, you do not need to be a professional or expert to use Access for some simple tasks. Access is great at comparing two sets of data and returning matches (or unmatched records). There are Wizards which will walk you right through it. I have taught people with very little Access knowledge how to do this. To fully design a complete database in Access, though, you should have a good understanding of relational databases, rules of normalization, and Access.

If (when) you have a good understanding of both programs, then which program you use is not determined by which one you are more comfortable with, but rather which program completes the task you want to accomplish most efficiently and easily.