Access or Excel for my project. Please give a newbie advice

[JanB]

Hello all,

I have recently started learning Access. So I am a newbie and have a question which program might be best for my project, as I am an experienced and above average user of Excel, including programming and Power Query.

Right now I have my project in Excel and it works (lots of VBA). But my gut feeling says Access is much more suitable.

I am not asking for the solution (although tips and tricks are very welcome) but for your feedback whether Access can handle it because of some complicated calculations.

I will briefly explain the purpose.

1) Lots of data from customers

2) Customers have property insurance and may have more than one house/apartment etc. (some have more than 100)

3) All those apartments need to be insured.

4) Depending on the type of insurance, clients have to pay a premium. This may vary from client to client. And some calculations are involved.

5) Clients need a certificate of all the property insured.

6) At the beginning of the year, all clients receive an invoice (an estimate of the premium for that year).

7) During the year there are many changes. Houses are sold and bought. The type of insurance may change. Customers come and go. So also plenty of proportional calculations (based on start and end dates).

8) At the end of the year, customers receive a new bill based on the actual premium. So they get some money back or have to pay an extra amount. (In Excel, I keep the values of bullet 6 on a separate sheet and subtract the values of bullet 8 from this to get the correct amount at the end of the year).

9) Invoices are automatically mailed to the customer.

10) A worksheet is created for accounting during invoicing so that it can be read into the accounting program by import.

11) It must be user friendly

Kind regards,
Jan

 

[Gasman]

Access can do all that and more, but it will mean you doing some hard graft to get it like that, plus you have to rethink the Access way and not the Excel way, which are very different. Your knowledge of VBA will be very handy, just need to lean the new commands and functions.
See here for links to normalization, relationships etc.

Until you get your structure correct, no point creating forms/reports etc.
Good luck, this is not an easy task for a beginner, but no doubt will be more flexible than the Excel version.

 

[Minty]

Access can handle complex calculations, although Excel has better in-built tools for certain things like amortisation etc.
Access will be a lot better from a data entry perspective, and generating reports.
When you start to get a lot of data, Access will handle it much more elegantly than Excel, and be more robust, particularly if you need to share the data.

Excel will be better/easier for graphs/dashboards if needed.

 

[mike60smart]

Hi
Can you upload an example Excel file with no confidential data to show your calculations.

 

[Cotswold]

Access a relational multi-user database where Excel is a single user flat file. If you are handling data particularly in many related tables, use a database. If you just need to cobble something together for your own use, then a spreadsheet maybe OK. Although probably slower for entering information. There is very little you cannot do in Access but there is a learning curve. You have a good start with your VBA experience, so you can build on that in Access.

 

[Pat Hartman]

If your Excel model needs to add columns/sheets to support additional time periods or different insurance policies, you will also need to adjust formulas - then Access will be a far better solution. Whenever you have to duplicate a formula in multiple cells, you run the risk of having some cells with an invalid formula. This would never happen with a relational database because you only have one version of any formula to apply to an entire set (table/query) of data. If you have to create a new workbook for each year, year over year reporting becomes very difficult. You also run the risk of losing one of the separate workbooks. If you make a change to the model this year, it doesn't affect previous years. Excel is not designed to be multi-user.

Excel merges the presentation with the data layer. This makes it more complex to create different reporting options and to optimize your data storage. It also means that data is duplicated. In a relational database, the data is normalized so that it is never duplicated. Updating a piece of data will happen in one and only one place and the change will be reflected for every other use of that data.

 

[Steve R.]

I have recently started learning Access. So I am a newbie and have a question which program might be best for my project, as I am an experienced and above average user of Excel, including programming and Power Query.

To answer your question at the fundamental level, you need a database management system, such as MS Access.
Going beyond your question as asked, you need to question if MS Access would be the most appropriate database management system.

You have already implied that you are knowledgeable concerning the MS Windows environment. So that may be the easier approach to pursue.

If you have time to explore alternative approachs to database management systems, you may want to examine using a database such as MariaDB in conjunction with a web browser. This would require a significant time investment along with a steep learning curve.

 

[Mike Krailo]

I have recently started learning Access. So I am a newbie and have a question which program might be best for my project, as I am an experienced and above average user of Excel, including programming and Power Query.

Right now I have my project in Excel and it works (lots of VBA). But my gut feeling says Access is much more suitable.

You are describing myself as I too got deep into Excel application programming years ago with VBA and created a fully functional system that pretty much did what I wanted it to do. Also got heavy into the Power Query as well. I then realized, it would be better if I re-created the project in Access and although it was a huge learning curve to get there, I'm so glad that I persevered through the pain of learning the in's and out's of MS Access VBA. I'm still tweaking things in that application but I was so happy the day the core functionality was working in Access, it was all worth it for me because all the data is in one singular place and much easier to manage now. Reporting is a breeze and creating new reports is easy once you have made a few of them. I still have reports that dump back into excel but that is no longer where my data is. I simply recreate a new Excel file with the new data from the database, and it works great.

 

[The_Doc_Man]

Your item #1 doesn't point anywhere.

Your items #2, #3, and #4 point fairly strongly towards Access.

Your items #5, #6, and #8 tend to point slightly more strongly to Access, which can generate reports more easily than could Excel.

The potential complexity of #7 tends to point more towards Access than to Excel though not a slam-dunk indication.

Access can easily build a mail message, even with attachments, and send it through Outlook for your #9, but Excel plus VBA could do that, too.

Access can easily export data to actual Excel spreadsheets to accommodate #10, though Excel with VBA can spin off new spreadsheets too.

Your item #11 tends to (slightly to moderately) point AWAY from Excel

I think that overall, there is more of an advantage for Access than for Excel.

If you are STARTING to learn Access, let me add something to your list of reading topics. Database normalization, when done properly, will make light work of tracking multiple layers ... like you have many clients and clients have many properties and properties have varying policies. Therefore, before you design anything, read up on "database normalization." You could just search this forum for normalization but if you go to the general web, include the word "database" in the search. At least six other disciplines also use the term "normalization" so you DO need to qualify a general search. The relational nature of Access databases will make a lot of your tasks easy when you want to gather together information from different but related tables. Excel, lacking this feature, has to search more often to find what you might need.

 

[Gasman]

Here are a few links that Micron posts about the topics The_Doc_Man mentoned.

Sadly Micron no longer frequents this forum, so https://www.accessforums.net/showthread.php?t=87892&p=510553#post510553

 

[LarryE]

Hello all,

I have recently started learning Access. So I am a newbie and have a question which program might be best for my project, as I am an experienced and above average user of Excel, including programming and Power Query.

Right now I have my project in Excel and it works (lots of VBA). But my gut feeling says Access is much more suitable.

I am not asking for the solution (although tips and tricks are very welcome) but for your feedback whether Access can handle it because of some complicated calculations.

I will briefly explain the purpose.

1) Lots of data from customers

2) Customers have property insurance and may have more than one house/apartment etc. (some have more than 100)

3) All those apartments need to be insured.

4) Depending on the type of insurance, clients have to pay a premium. This may vary from client to client. And some calculations are involved.

5) Clients need a certificate of all the property insured.

6) At the beginning of the year, all clients receive an invoice (an estimate of the premium for that year).

7) During the year there are many changes. Houses are sold and bought. The type of insurance may change. Customers come and go. So also plenty of proportional calculations (based on start and end dates).

8) At the end of the year, customers receive a new bill based on the actual premium. So they get some money back or have to pay an extra amount. (In Excel, I keep the values of bullet 6 on a separate sheet and subtract the values of bullet 8 from this to get the correct amount at the end of the year).

9) Invoices are automatically mailed to the customer.

10) A worksheet is created for accounting during invoicing so that it can be read into the accounting program by import.

11) It must be user friendly

Kind regards,
Jan

Developing an ACCESS system always follows the business operation process. So if:

1. Each customer may have one or more insurance policies
2. Each insurance policy may cover one or more apartment buildings
3. Each insurance policy may have one or more start and expiration dates and policy limits (and other characteistics)

At the very least, you will need:

1. A customer information table
2. An insurance policy table (because each customer may have one or more policies)
3. An an insurance policy detail table (because each policy will have separate start dates, expiration dates, policy limits, and premiums etc.)
4. An apartment building table (because each policy may cover one or more apartment buildings)

Does this sound accurate?

 

[LarryE]

@JanB:
Are you still seeking advice for your project?

 

[pdanes]

One thing I've not seen anyone else mention - Excel makes it VERY easy to trash your data. Sorting only some columns and subsequently saving and closing the workbook will mix your data to the point where you are very unlikely to be able to recover it. This simply can't happen in Access.

I would incline to store data and do general user manipulations in Access, using forms and queries, then use whatever else is appropriate for whatever other tasks are required. The entire Office pack is highly inter-operable - any Office application can spawn any other Office application and manipulate it. Your Access database can create an Excel Spreadsheet, a Word document, Outlook mails. You can prepare Excel templates for any highly complex calculations, then use the Access database to call the template and insert data into it from the database. Calculated data from that can then be loaded into a Word document for presentation purpose, and sent off to an Outlook mailing list. All you need is the proper libraries loaded - each app starts with the default libraries loaded for its own objects, but you can add whatever libraries you need for manipulating and other Office object you want. It means lots of VBA coding, but once you get a good grip on that, you will be amazed at what you can do with it.

Also, if you think ahead, you can set it up to make it easy for future migration to a true client-server database. Projects of this sort tend to take on a life of their own - I would not be at all surprised if somewhere in the not-too-distant future, you will find that Access can longer gracefully handle the number of users and traffic the project grows into. You will then be faced with expanding to SQL Server (or maybe some similar engine, but SQL Server plays very nicely with Access). If you set things up with most of the work going through views, rather than going directly to tables, such a subsequent migration will be easier.

But mostly, as several other people have written, read up on normalization and pay close attention to that as you design. Screwing that up will mean an error-prone app, and lots of extra work necessary to overcome the design shortcomings. Getting it right will make lots of things almost trivially easy.