How should I design an MS Access application for Excel users?

Question

I work for a public health study, and I have been asked to help manage the workflow for an upcoming project. We will be choosing subjects, tracking our contact with them, interviewing them, placing them in one of several interventions, etc. Previously the staff used a large number of Excel spreadsheets, but I was asked to help reduce data duplication and help automate processes.

My initial thought was to use MS Access to develop a custom application. But I worry that the staff will dislike being asked to switch from Excel, where everything is flexible, to Access, where everything is structured.

For example, one way they kept track of subject status was to color rows in Excel. This wasn't an ideal situation - other users didn't know what row colors meant - but it was very quick and easy. Or, as a second example, a certain staff member makes many phone calls to subjects. When a subject calls back, she must quickly find that subject's name. This was easy in Excel - she simply pressed Cntl-F to find the number on her caller ID - but in Access even small things like this must be manually coded.

I considered trying to make Excel function as a database, but decided it would be too unwieldy. Another consideration is that I'm only here temporarily, so I may not be able to support the application in the future.

Do you know of any form components, design techniques, standard practices, or ideas for helping to smooth the transition? How can I have both structured, automated processes, and a usable interface?

Answer 1

...but I was asked to help reduce data duplication and help automate processes.

Well, if that is your job description, then the staff simply has to deal with a new system. Of course, assuming you will do a good job.

And finally, this is a temporary job, so I won't be around to support the application permanently, so take that into consideration.

For now, it is just an open question whether there is support needed in future. First, analyse how the functionality of the present Excel sheets have grown in the past. If not so much, or not at all, then there is a huge probability that a new Access application might suffice for years.

Although things are structured underneath in Access, what your user interface will present is something completely different and could become as flexible as you like. For instance: it is perfectly possible to offer all editable fields in a formattable way like RichEdit controls. Though it requires some above average VBA skills and probably considerable implementation time.

A much more easy approach is to present the forms in data view so they have the exact same appearance as they have now in Excel. You could also provide an option to switch between views. That would provide some comfort for the users after the transition. The in-between solution is to design the forms in table figuration which results in the same appearance, but in more control over the behavior. But a columned layout should be preferred; the users just have to become familiar with it.

About your two examples:

• Offer and present a legend for the possible row colors. Use a listbox or toolbar to add or delete the row color of the current record(s). Offer customization for the colors and color descriptions with a separate form.
• CTRL-F works just fine for forms. You may want to present a custom find dialog, but the default dialog offers enough capabilities without being too complicated. But preferably there would be a search box at the top of your form, or even at the top of every column, to eliminate the need for that keyboard shortcut.

There is just so much more possible with Access...

Finally, consider suppressing all default Access commands and menu-bars, because it is very easy to screw up the whole database and/or application.

Answer 2

Apart from what NGLN has already described. You could create input forms in Access to have the input part 'fixed', and then you can create Queries in Excel to access that data and have all the flexibility you want! I'd keep these queries read-only though.

A large number of users? Are they all on-site? Access is not a good choice for multi-site data access. For a serious application, I'd use SQL-Server, but that has nothing to do with UX. The possibility to create Excel Queries remains the same with SQL-Server.