Access vs Excel

cyliyu

New Member
Joined
Jul 7, 2015
Messages
14
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?
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,643
Office Version
365
Platform
Windows
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

New Member
Joined
Jul 7, 2015
Messages
14
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,643
Office Version
365
Platform
Windows
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!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,643
Office Version
365
Platform
Windows
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).
 
Last edited:

cyliyu

New Member
Joined
Jul 7, 2015
Messages
14
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,050
Messages
5,484,410
Members
407,438
Latest member
DKrakken

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top