Access vs Excel

[cyliyu]

I have a Excel spreadsheet for users to input the data and it came with all the formula for auto calculation etc.
The file size is getting big.
I was thinking to learn and switch to MS Access and would like know can Access able to perform the auto calculation as what Excel does?

 

[Joe4]

It is important to understand that Access is not simply an extension of Excel. They are two entirely different programs, with different purposes.
Excel is a spreadsheet program. Access is a relational database program.
I often see people use Excel for relational database. It can be done, but it is usually clunky and cumbersome, and Access is the better choice, as it is built for that and handles it "naturally".

You haven't really told us much about your project. It COULD be a relational database program, in which Access would be the better choice. But I think we need to understand it better.
Can you describe it in more detail for us?
Also, include things like:
- How much data are we talking about (number of rows/columns)?
- What these calculations represent?
- What exactly you are doing with this information?
- How data is being loaded into the program (import or data entry)?

Note that Access does most of its calculations in queries, and not directly in the table itself (newer versions of Access allow for some basic calculations at the table level, but it is best to avoid using this - it kind of violates Rules of Normalization, and no other relational database programs support this). This is never really a reason to do the calculations at the table level.

 

[cyliyu]

Thanks for your reply.
i use Excel as a database for users to input the “necessary” data such as date, car number, serial number, date code, fault description, finding etc.
on the other hand, there are columns with formulas, “protected cell” to calculate the number of day the same serial number recurring and number of time the same serial number occurred. etc.
Total number of columns about 30 per sheet.
The row will keep on increasing when more data are input by the users.
There are about 20 similar sheets for different product.
Data are lookup from one sheet in another (max 3 sheets) to check for some related info.
i have pivot table and pivot chart for different product too.

I may be able to do it in access but the only worry is the formulas which I am not sure can access do it especially If i need to compare the data from 1st row to the last row of the data entered.

any suggestion or input definitely help. Thanks.

 

[Joe4]

Yes. one of the big differences between the two is how they handle data. In Access (and other relational database programs), the order of the data within the table really has no meaning/bearing (someone one described it as a "bag of marbles", with all the records mixed in in no particular order). This can make things like cumulative ordering and comparing to previous record (order-wise) a bit challenging. Sometimes, I use VBA to order the records in a DAO Recordset, and iterate through them.

Things like "lookups" are actually much easier to do in Access then they are in Excel, by nature of being a "relational" database. Instead of having to do VLOOKUP or INDEX/MATCH formulas, you simply join two tables on common field(s).

The most important thing when using Access is to design your data tables in a manner which will allow you to complete most tasks without too much additional effort. Table Design is EXTREMELY important. There are "Rules of Normalization" which should be followed (you can Google that term). Two keys are:
1. You should NOT have multiple tables with the same structure. If you do, they should probably be one table, maybe adding another "identifying" field if you need to differentiate them (i.e. you would have different tables for different years data, you would simply have one table with a year field - you can filter them out using Queries).
2. Queries are where most of your work happens, filters, calculations, grouping, etc.

I hope this help gives you a start. It is important to design it right, or you will have a difficult time. It happened to me the first time I built an Access database!

 

[alansidman]

Here is a link to start learning Access.

http://www.accessmvp.com/strive4peace/

 

[Joe4]

Yes, it should be mentioned that the learning curve in Access is a bit steeper than it is more Excel.
If you just jump into it and try to design a database without knowing much about Access or Relational Database Theory, you will likely run into some frustration (I have been there/done that).

It works great once you have it set up correctly, but you have to get to that point first! There really aren't any shortcuts without learning it (other than hiring somewhere to do it for you, or enlisting the help of a consultant to help you).

 

[cyliyu]

Thanks both for your greatly help and advise.
i May need to start consider switching from Excel, maybe start with a simple database creation to have a feel before converting my existing Excel database to Access.