Customer case study: Moving from Excel to Access

Sometimes a business solution is created by using Excel when Access is more appropriate. Or, perhaps Excel was originally a good idea, but the data has outgrown an Excel solution and it is time to use a database program. Fortunately, you can move your business data from Excel to Access.

Cancer Lifeline, a non-profit organization based in Seattle, originally designed its grant-tracking system by using Excel. After running into trouble with data redundancy, they decided it was time to give Access a try.

Dr. Kerry Fowler provides volunteer IT support for Cancer Lifeline. He agreed to an interview in which we discussed Cancer Lifeline's IT needs, his role and technical background, how they used Excel, and what they have done with Access.

The interview, part 1

ST: Okay, well, I'm Steven Thomas and I write for Office Online, and I'm here today with Dr. Kerry Fowler at Cancer Lifeline. Cancer Lifeline is a non-profit organization that helps people live with cancer.

ST: Dr. Fowler, can you describe a little bit about what kind of support Cancer Lifeline offers to people?

KF: Well Steven, Cancer Lifeline offers non-medical support to people living with cancer which includes patients, family, friends and co-workers, so it's quite a broad spectrum.

ST: So when you say non-medical what do you mean?

KF: People don't come to Cancer Lifeline to get medical advice, but they come for classes, there are exercise classes, yoga, writing classes, flower arranging, a number of things to build community among cancer patients, and to interact with other people with cancer.

ST: Well that sounds like a wide variety of very important services, thank you for that information. So, you have a degree, in bio-informatics, is that right?

KF: Actually it's organic chemistry.

ST: Organic chemistry, okay sorry.

KF: Nothing to apologize for.

ST: So how does that help you here at Cancer Lifeline?

KF: Well I was in the pharmaceutical industry and drug discovery for quite a few years, and when my job went away as part of a corporate merger I took some time to volunteer here at Cancer Lifeline where my wife is the CEO, and I had been doing some chemical informatics over the past 10 years, so I was pretty comfortable with handling information, primarily big flat files, and I had to learn some UNIX commands and that sort of thing, but I didn't have any experience with relational databases.

ST: Ok, well can you tell me a little bit about the sort of information management tasks that you do here as part of your volunteering?

KF: Sure, when I first came I was doing just everyday IT things, you know, is the printer working, my mouse doesn't work. But eventually I saw that there were some functions going on, for example the patient assistance fund, where people were using Excel spreadsheets to store information going back several years about checks that had been sent out to people to help them pay for a mortgage, or for medical bills or for transportation.

ST: So a lot of different files?

KF: Well there were a lot of entries in the spreadsheet.

ST: So one big spreadsheet?

KF: Yeah one big spreadsheet with a lot of tabs, but it was difficult for them to tell how much money has this person gotten this year, how much have they gotten over the past 4 years, how many people are getting help with their mortgage?

ST: Sure that sounds like classic problems you can have, putting a whole lot of data in Excel and trying to drill down into it, into one spreadsheet like that.

KF: So that looked like a disaster waiting to happen, just one bad sort away from complete catastrophe, so I decided to dive into learning Microsoft Access to solve this problem. The other thing that was happening was data was being re-entered multiple times.

ST: What kind of data?

KF: Just names, people's names, zip codes, social worker's names, any number of things.

ST: So the same person would end up with a couple of different records perhaps?

KF: Exactly, you get very creative spellings of Seattle for example.

ST: Sure, so why Access? Did you just have it here or did you acquire it for this purpose, or what made you come to Access?

KF: Well Access is widely available here because Cancer LifeLine uses Microsoft Office and they use Small Business Server 2003, so every computer in the place has Access, and I had looked into Access just on my own about a year ago but I really didn't do any development, so I thought this was a learning opportunity for me and it was a way to possibly help Cancer LifeLine meet their goals.

ST: Cool, well I think you've made a good decision. I have a little bit of experience working with Access, and not quite as much working with Excel, but I know what Access is for and it's definitely better suited to the kind of management tasks you were talking about.

ST: Let's take a little break and then come back for the second half of this interview.

The interview, part 2

ST: Okay, well, this is Steven Thomas again, back with the second part of my interview of Dr. Kerry Fowler at Cancer Lifeline.

ST: Let's talk about the process of moving from Excel to Access. How much work would you say was involved? Are you finished with the work?

KF: I think you're never quite finished with the work, especially when you're dealing with legacy data that was in a spreadsheet where there may be strange punctuation that you don't want. Overall I think the thing that I found most challenging was just matching, well I'm going to flip it around here for a while. I think that making the data types match is important, that's a dumb thing to say.

ST: It's not a dumb thing to say, it's important and it's challenging to get them right, because Excel is a bit more forgiving about what data it will and won't accept.

KF: Right yes, cleaning up the data, what I found has been most challenging is taking the old data, removing duplicates, finding spelling errors, finding strange punctuation and getting things tidied up so it's easier to import into Access and doesn't cause errors. So there's actually a lot of work that has to go into the Excel spreadsheet or the text file that you're importing. Once everything is matched up the import is pretty easy.

ST: So has this put business processes on hold in the interim?

KF: No, people have been working around, one of the things I've done in addition to working on a patient assistance database is taking an older Access database that they had that's essentially a big mailing list, and it was just a big flat file. So I'm taking some steps to clean that up and make it easier to search for duplicates.

ST: But they're using the patient assistance fund database now?

KF: Yes.

ST: But there's still data in Excel that you haven't imported into that yet, it's legacy data that you still are going to import.

KF: That's true. I have the 2008 information imported, but I still have the 2004 to 2007 data to clean up and import.

ST: Okay, so people weren't having to wait or anything?

KF: No. It's fine for current use.

ST: So what other challenges did you face, or are you facing building the new system, including this other one that you're talking about.

KF: Well there's some things that are specific to Access, I had some challenge with some of the Visual Basic things and also doing Dlookup() and making that work right, and in certain cases you have to get the quotes and the square brackets all in the right places or they don't work. But overall it's been a lot easier than I thought it would be and it's very rewarding to be able to make something that works.

ST: So are you doing anything with Access that you weren't doing with Excel because it wasn't practical? Anything new besides the obvious, you know putting data in different tables and that sort of thing?

KF: Well one of the things I figured out how to do which I know would be obvious to an experienced user but, for example, the zip code is redundant information with regards to the city and the state and the county, so I've streamlined the data input for mailing lists so that only the zip code needs to be imported, and then I have a table of all the US zip codes with their state, city, and county. So that simplified things a lot and reduces spelling errors.

ST: That's great. How about reporting? Have you seen any gains in your ability to produce reports now that you've moved to Access?

KF: Yes I mentioned before that it was difficult when using the Excel spreadsheets to determine how much money had been given to a particular person in a particular time period, but now with the Access system I've been able to generate some reports that automatically generate check requests, code them for the appropriate type of cancer that they have, and it also generates a summary sheet for the finance department of all the different checks that have been written that week for each of the accounts and the subtotals.

ST: Wow that sounds like a nice gain. So finally, what have your colleagues here been saying? Has anybody given you any feedback on both the process and the product that you built for them?

KF: Well they really love it. For the patient assistance fund it's really streamlined the process. Because there are fewer errors it means that the checks can get written more quickly, and it really was a problem before, information would have to be sent back from finance for correction, and that doesn't happen as often now, so the clients are actually benefitting directly which is very nice.

ST: That is awesome, that's very nice. Ok well thank you very much for the interview and we'll be in touch again soon I'm sure, to check in on your progress with the patient assistance fund, or, what is the new one you're working on again?

KF: The new one is the big mailing list.

ST: The mailing list database. Ok well I'd be interested to check back in and see how that's going, thank you again.

KF: My pleasure.