Access vs Excel Database for Mass Emailings

[Niner]

I've developed a database in excel of our subcontractors that our company uses to issue mass email notifications for our projects. It can be easily corrupted by our users so I've been seeking resolution and wondering if Access may be the answer.

Our excel list has a lot of macros, conditional formatting, features, and abilities which I'm not sure are available with Access. I would greatly appreciate your expertise before I possibly head down the wrong path, only to find out that Access is unable to conduct what we need it to.

Our database currently has 2500 companies with 4000 contacts and it's growing quickly as we expand into different regions.

This database and process is critical for our business as it is how we stay in contact and obtain future work.

Process for using our sublist;
- Select which trades will be affected by the project so you don't have to review all of the contacts.
- Sort and display the contacts by various categories, including constantly updated performance ratings, to narrow down the list.
- Companies or contacts are displayed with color codings on whether or not they should be contacted for different reasons.
- Ability to select and display if they are invited for the project or if they've declined.
- Ability to make notes, and sort per notes, depending on correspondence with those invited.
- Easily able to keep a separate database per project that does not affect or change the main company database.
- Able to use Email Merge to send out mass emails for notifications. We typically send out 600-2500 emails each time.

I've attached images of Worksheets within our Workbook to hopefully explain.



Can this be done with Access?

[Bob Fitz]

How many users will have access to the database and will they all have a wired connection to the db.

[CJ_London]

I'm sure Access can do what you want. I don't see anything from your post that would be a problem - but if you are new to access you will need to throw away excel thinking so may have a steep learning curve.

For example, from your screenshot you would need a table for companies, perhaps another for locations if they can have more than one location, a table for contacts which may or may not include email, office phone and mobile - depends if they can have more than one and what it is associated with (company?, location? contact?). So in Excel you have many rows showing the same company name and phone number. In Access you would have one row (record) but when linked to contacts using a query you would be displaying the company and phone for each contact - a slightly different way of looking at how the data is organised. If the office phone number changes, it changes for all related contacts

Excel tends to combine data and presentation in one view (wide and short) whilst Access stores data in tables and presents via forms and reports which link to the tables using queries.

However you say your excel file can be easily corrupted by your users. Depends what you mean by corruption but Excel can be locked down (not as securely as Access, but would give users pause for thought before making a change). Might be worth reviewing what is considered corruption and how it happens then whether you can apply some additional security before leaping into Access.

[Niner]

Not sure I understand as I'm not quite sure how Access works amongst other users but here is how our Excel Sublist is currently set up. We use OneDrive so that everyone in the company can open any of the excel documents simultaneously.

* We have a main Sublist that is updated daily with contact revisions. I'm in control of this database to try and prevent corruption and it has many backups.
* When someone needs to use the list for their individual project, they make a copy of the main Sublist and save it in their specific job folders. Any future updates to contacts needs to be completed by them.
* Everyone in the company has access to it through either our servers or the OneDrive, which is around 60. There are currently 8 primary employees who use this list consistently, but I'd like to make it simple enough so that everyone is fairly comfortable in using it. As much as we've tried to simplify it, and provide extensive training, they are currently intimidated by the excel sheet, unless they use it frequently.

The list is important enough that I've hired someone whose primary responsibility is to stay in contact with those on the list, and provide updates, as there is a lot of turnover and ever changing companies in our industry.

[Minty]

We use OneDrive so that everyone in the company can open any of the excel documents simultaneously.

And this is what is causing you to have corruption, sharing open files is fraught with difficulties, that even Microsoft can't easily overcome.
A well designed database won't have these issues and allows many people to view and use the data, without the same conflicts.

[Micron]

I don't see anything from your post that would be a problem

Maybe it's not really a problem, but one db for each project should be a no-go. It's one for one and one for all. Also, One Drive for using Access is a non-starter. If you need remote access to the db, there are other ways but not that one. It's a sure-fired recipe for corruption and introduces a requirement to sync data if more than one person can simultaneously use the db.

As noted, Access has a large learning curve but it is quite robust in what you can do. How easy it is made for everyone to use is the responsibility of the designer. I have built Access databases for contracting (on-site and off-site) and was a coordinator for both aspects in the latter part of my career so I might know a bit about what's ahead of you but certainly not enough to know all the specifics of your operation.

If you want a flavour of how Access is different, start by studying database normalization.

[Niner]

- but if you are new to access you will need to throw away excel thinking so may have a steep learning curve.

In Access you would have one row (record) but when linked to contacts using a query you would be displaying the company and phone for each contact - a slightly different way of looking at how the data is organised. If the office phone number changes, it changes for all related contacts

However you say your excel file can be easily corrupted by your users. Depends what you mean by corruption but Excel can be locked down (not as securely as Access, but would give users pause for thought before making a change). Might be worth reviewing what is considered corruption and how it happens then whether you can apply some additional security before leaping into Access.

Thank you and understood. I was expecting a steep learning curve, and the reason I posted this to you guys, as I didn't want to get knee deep into something completely different and then realize, when it's too late, that it couldn't do what we need it to.

Having something that changes office numbers for all related contacts would be a big benefit which gives me hope. That is some of the corruption I'm talking about. Along the lines of users inputting data in their own way instead of following a template or guidelines.

I've tried all kinds to securing or locking cells, worksheets, and workbooks but it always creates limitations or just simply doesn't work with what we need. I've had several experts look at it and they've simply said that it's not possible with our setup and the way we operate.

-----

I also wanted to check if Access allows us to make various organized notes that could be sorted and issued in a report. During the initial stages of a project, we keep track of the participation level. A lot of this stems from our client's requests. We do this with easily hidden rows and columns as it's not always required;

[Micron]

Having notes will be the one of the least difficult aspects of your design.

[CJ_London]

I also wanted to check if Access allows us to make various organized notes that could be sorted and issued in a report.

easily done in Access.

Before you start, you need to map out the business process and the related data. The objective is to store data only once (a customer name for example). Any table needs a PK (Primary Key) to uniquely identify a record. This is usually an auto number and should not be assigned any meaning whatsoever (other than as a unique identifier) and would not normally be visible to the user. child tables (e,g, contacts) will have their own PK and also a FK (Foreign Key) which stores the PK of the parent record - and again, would not normally be visible to the user. Google 'database normalisation' to get an idea of what is involved, it's not complicated.

In the past I've used post it notes on a wall, each note representing a single piece of information - such as customer name, customer address, an invoice date, a product code. These are grouped into areas on the wall that will eventually represent the tables. You then draw lines between these tables to represent the joins

In Access you have the relationships window. So perhaps create a few tables, open the relationships window and drag all the tables onto it. You can the connect the customerPK of the customer table to the customerFK of of the contact table. If you need to add another field, rename it, whatever you can do so from the relationships window, right click on the table and select table design.

A few tips: Tables are about storing data, not presentation

1. You don't care what the data looks like, so long as it is accurate. So don't apply formatting to number and date fields, don't use lookup fields and don't use calculated fields. If you do, it will bite you in the bum at some point because what you are seeing is not the true value.

2. give your fields meaningful names within the context of the whole app, not just the table. Date? Date of what? ID? ID of what? use invoiceDate or invDate, customerID, contactID or better customerPK and customerFK so you know which end of the join it relates to

3. Don't use spaces or non alphanumeric characters in field and table names otherwise you will have to use square brackets and even then they can cause unexpected issues. You can use the underscore if you must, but camelCase is much easier to read than camel_case

4. Don't use reserved words - there are many but the ones used by newbies will often be name, date, desc, description - see this link https://learn.microsoft.com/en-us/of...reserved-words

5. Keep field and table names brief without being obscure. Nothing worse than tblWhatHappenedLastYear

6. Data is stored vertically so don't use the field name as part of the data, include an extra column. e.g. if you have fields dateOfFirstVisit, dateOfSecondVisit, etc you are slipping into the excel way of thinking. What you should have is a separate table with fields say VisitType, VisitDate (plus an FK to link back to the parent)

[Niner]

easily done in Access.

Before you start,

That is huge and much appreciated. I will definitely use this for reference. I'm nervous and excited at the same time.

I understand the naming schemes as this is how I keep track of my numerous macros and named cells but I'll make sure to follow your examples.

--------

Next question involves legacy... Just like my Excel Workbooks, I'm sure I'll be the helpdesk. I need to look out for our company's future. Would it be better for us to higher a programmer to develop and maintain this?

If I develop this, upon my departure eventually, I could see our company fret at maintaining this database. At least with my current Excel, they can easily transfer or use the information until they find or develop another solution if need be. Which approach would be better for us overall?

[Welshgasman]

Have a look at a few of these videos.
Also plenty other videos on Youtube about various aspects of Access.

https://www.youtube.com/results?sear...esign+database

[Micron]

Whether to contract that out depends on factors that really only you and your associates can say you should do one or the other. If you contract out, every time a bug arises, or someone uncovers something that doesn't work as intended, or items get added to a wish list, you're calling back that developer. There is no more guarantee that developer will remain available for as long as you require, which basically puts the company in the same spot as if you had developed and left. One key factor is to include notes to describe the code and what it is supposed to be doing (and code is really the only path to take for a serious application) so that those following don't have to make sense of a mess. Learning code would add to your learning curve if you take this on, but macros (a different thing in Access) are not for an application the likes of which you want to build or have built for you.

[Welshgasman]

Also be aware that 'macros' in Excel are VBA, whilst 'macros' in Access are a completely different beast, and I for one believe you would be better off learning the VBA aspect of Access, which should be familiar to you, as opposed to Access macros.

[CJ_London]

As an independent developer I was asked to take over an existing database that managed the client's theatre and had been developed by one of their employees. It worked well for their requirements, even after that employee left, but they had the idea they could sell the app to other theatres. This would involve among other things a completely different seating plan for different theatres and a different way of seating management. Unfortunately the original developer only used access macros without any documentation. I quoted for the cost to provide the documentation and from that could then quote for modifications to meet other client requirements and they decided selling the app was not economic for them. To this day, they continue to use the app but with no idea how it actually works (and neither do I).

So agree with the comments - don't use access macro's, use vba and make sure you document what the code is intended to do.

I have several clients I support, some I have developed the app for them, some I have taken over an existing app - but in the latter case, not before I ensure it is properly documented or the client is prepared to pay for the documentation.

I partially agree with the comment about what is the difference between you developing and then leaving and an appointed developer developing and then retiring/getting run over by a bus. But I can quote examples where a company with many employees has been appointed to develop an app, perhaps by modifying an existing app, only for that company to either a) lose the key employee who knew how it worked, b) goes into liquidation or c) gets taken over and the new owner who says 'we're not doing that anymore'

It really depends on what your app is required to do versus existing offerings from companies who offer apps providing much the same functionality. Most online applications will do around 80% of what you require. the other 20% you either live with or pay a not insignificant amount of money for customisation.

And that comes down to USP. Does your app as works/envisaged provide a Unique Selling Point to your clients? Maybe it reduces costs to them, provides a faster or more informed service or perhaps just a better client experience. That depends on you and your clients.

[Niner]

A

It really depends on what your app is required to do versus existing offerings from companies who offer apps providing much the same functionality. Most online applications will do around 80% of what you require. the other 20% you either live with or pay a not insignificant amount of money for customisation.

Thank you as that is beneficial.

Our excel was created because the online applications are very ineffective in getting through to our clients. Has nothing to do with costs. After years of trying the majority, they all failed miserably. It's a long explanation and I don't want to get sidetracked.

We're not interested in selling it. It gives us a huge advantage in our highly competitive world.

I'm definitely keeping all of this in mind as we move forward.


Thank you to all