Access vs Excel
Results 1 to 7 of 7

Thread: Access vs Excel

  1. #1
    New Member
    Join Date
    Jul 2015
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Access vs Excel

    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?

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,383
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Access vs Excel

    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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Jul 2015
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access vs Excel

    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.

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,383
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Access vs Excel

    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!
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,880
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access vs Excel

    Here is a link to start learning Access.

    http://www.accessmvp.com/strive4peace/
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


  6. #6
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,383
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Access vs Excel

    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 by Joe4; Jul 29th, 2019 at 10:50 AM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    New Member
    Join Date
    Jul 2015
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access vs Excel

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •