3

I'm working with several Excel worksheets and workbooks that need to consolidated or linked to each other. These workbooks will track client interactions for different team members.

I did the initial consolidation in Microsoft Excel, but realized that going forward, there's a risk of people overwriting each other or working from an older version of the master workbook.

After some research, I realized one idea could be to have each person track their own client interactions. I can regularly consolidate each workbook into the master using Excel data models.

But are there additional risk to doing this? Is an Excel data model as good as an Access database? I've shied away from Access because my team is not comfortable using SQL and overall dislike the Access Interface.

This is why I started off using Excel. But managing multiple workbooks (with changing names) has already created several version control issues. Any feedback would be helpful!

I'm limited to using Microsoft Office 365.


Use Cases

Log interactions

  • Each person can keep a log of daily client interactions
  • Individual logs are regularly synced to a master list that shows interactions for the whole team

Log contact information

  • Each person can contact information for new clients
  • New contact info is synced with the master list

Add or update broad company information

  • Each person can add or update general company information (ex Company A is focused on xyz for 2020). This is separate from the individual interactions
  • Updates to general company information can be added directly or synced with a master list

Filter for a list of interactions, contacts and broad information

  • Search interactions based on criteria (ex company's city, interaction date, contact's place of employment)
  • Print all information (interactions, contacts and broad information) based on a specific company name

Reporting

  • Send scheduled reports (weekly, quarterly) based on recent updates and other specified criteria (ex company with the most logged interactions for the month)
  • They are two different things, so one is not inherently better than the other. But it does sound like you need something that can handle multiple users. – GrandmasterB Feb 5 at 19:21
  • Thanks for replying! It's not that it needs to handle different users. Since everyone will have access to the file (no login necessary), it needs to have a pretty simple interface, as well as generate reports about any updates/ activity – Jasterix Feb 5 at 19:23
  • For what purpose? Neither should be used for a production database, but for one-off tools one will fit your needs better than the other. Identify your needs. – Berin Loritsch Feb 5 at 19:26
  • @BerinLoritsch This was great feedback. I've added some use cases. This would be very much a tool for right now, to get us through the next few months – Jasterix Feb 5 at 19:42
  • 1
    Seven users seems reasonable for an Access application. You will need to have some basic knowledge of how databases work first, though. I see at least 3 different tables in your use cases: Companies, Contacts, and Interactions. – Robert Harvey Feb 5 at 19:51
4

I've shied away from Access because my team is not comfortable using SQL and overall dislike the Access Interface.

Here's your answer. There is a strong argument to use Excel because it is what your team is comfortable with, and that is pretty darned valuable.

But managing multiple workbooks (with changing names) has already created several version control issues.

You can use an Excel front end and have it connect to an Access database behind the scenes. If you store everything centrally then a lot of the versioning issues will be much easier to deal with-- for example, you can release new versions of your Excel workbook with new code, without users having to move their data. It also allows you to store data in real time, meaning that you don't have to deal with merges (and merge exceptions) any more.

  • That sounds great! Unfortunately I don't have a high enough reputation to upvote the reply :( – Jasterix Feb 5 at 20:20
  • 1
    Access user don't need sql! MDAC wouldn't be my choice, if serverless try SQLite. – ComputerVersteher Feb 5 at 20:21
2

The datamodel is (mostly) independent of the database. Excel is not a database.

Ms Access is a great RAD (rapid app devel) frontend, but db (MDAC/Jet) is limited on multiuser, security and backup (no dumps, binary log, etc), but fast and no db-server needed!

You can work with Excel, but fetch data from a db (company enviroment Sql Server, MySql, Postgre are recommended (besides other)) as for speed, function, security and backup from a data connection. Also the MDAC connectors bitness issue is sad! Connectors (ODBC/ADO) can't be used side by side for x86/x64 apps, because you can only install one bitness, limiied to apps of that bitness (this is a killer of MDAC at all)!

Basic SQL is mandatory (for devel, not user), but you will see the benefits in pure excel too, as you can use sql on it to simplify tasks (no more loops in loops on cells).

First to master is database normalization, then decide the rest. I recommend Access as you will bind data to form (updates/inserts your changes back to db) within seconds. Try that on excel

  • This is a great answer! I'll take a look at database normalization. Unfortunately though I don't have a high enough reputation to upvote this – Jasterix Feb 5 at 20:21
  • 1
    @Jasterix You can upvote an accepted answer! – ComputerVersteher Feb 5 at 20:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.