Integrating Databases In Your Office - With Steve Sanchez
11601 Plano Rd. Suite 107
Dallas, TX 75243
Fax: (214) 343-3988
Scott Cytron: Good afternoon everyone! Today, we have the opportunity to talk with Steve Sanchez, a partner with InfoSolve, Inc. a Dallas-based IT firm which specializes in designing, developing, and maintaining database applications, connecting databases to the Internet, and integrating database applications. While InfoSolve is a Microsoft Certified Solution Provider with programmers experienced in designing and building database applications with Active Server Page or Visual Basic front ends and SQL Server, or Access backends, the company brings far more to the table than skilled programmers; its consultants have business skills that enable them to provide solutions that make sense from a business perspective, not just from a programming perspective.
Steve is a graduate from Columbia University who has been in the computer industry for about four years. He enjoys helping people find the solution to their technology problems.
Steve Sanchez: Thanks Scott, It is a pleasure to "chat" with you all.
That's right the technology industry has really changed, and many people don't realize that there are good solutions to many efficiency problems they face in the office.
InfoSolve's specialty is databases.
You may or may not realize that many office applications are at their core big databases. By integrating these databases together, and adding new functionality to them a company can increase the efficiency of it's operations, cut costs, and make it's employees lives easier!
Michelle French: I love to hear "making my life easier"
Dblevins: Do you mean databases like Access? We use that.
Steve Sanchez: How many of you are familiar with the types of database applications your companies use?
Michelle French: I am!
Michelle Kaye: I am
Jerry Slowey: I am
Jason Floreano: I am
Dblevins: Me too.
Lynn Stewart: I am.
Steve Sanchez: Ok dblevins mentioned Access that's one kind what else are you useing out there?
Tiffanye Warren: Microsoft Outlook
Michelle French: I believe all of ours are Access based: VPM?
Michelle Kaye: We use Access.
Lynn Stewart: We are starting to use Goldmine.
Kristy Muller: Act and Goldmine.
Jim Rodgers: Act!
Shirley Connelly: We use several, primarily Access.
Steve Sanchez: Tiffanye mentioned Outlook. Someone else mentioned goldmine...Most people think of their contact managers when they think about databases, but what about your Accounting programs?
Steve Sanchez: Accounting applications are big databases.
Shirley Connelly: sure, mas90, cpas, quickbooks, peachtree, etc....
Phil O'Brien-Moran: We use SQL Server, RMS & Codebase(XBase) depending on the application requirements.
Steve Sanchez: Yes MAS90, Solomon IV, Great Plains, etc...
Stuart Avera: We integrate PACS databases with Lotus notes.
JK: How do you know if a given accounting package can have Web access?
Dblevins: We have the need to share some of our accounting infomation with our clients. Is this possible with an off-the-shelf package?
Steve Sanchez: In many cases it is the integration of THESE applications with others that can really have some benefits. Let me give you an example for data collecting...DBlevins I'll get to that in a minute.....
Let's take time and expense reporting for instance. The usual process involves lots of forms to fill out and submit correct? Are those forms easy to deal with? Employees fill out forms, collect receipts, submit the forms and receipts and then wait. In accounting, someone gets the “submission” and then proceeds to tally it all up make sure the numbers are right, cross check the receipts and then cut a check.
Michelle French: And we enter & enter again for correctness... ARGH
Steve Sanchez: What if some of that work could be transferred a database system that has been INTEGRATED with a web browser? For example, with an accounting package that was connected to a web browser the employee could visit the company’s website and go to a web page specifically designed for expense reporting. He or she could enter their information into the web page. The web page would have all the business rules written right into it so when he tried to enter in an amount that was not allowed it would flag it. It would total everything, and it would send that information directly into the accounting program! The accounting department now only has to review the information, compare it with the receipts and cut the check. The process of entering the information and tabulating it is removed.
One of the big time and efficiency savers here is that the information is entered into the system only ONCE!
JK: Will what you are describing work with Solomon? QuickBooks Pro?
Steve Sanchez: Good Question! That takes us off into some of the technology behind these databases.
Scott Cytron: Steve, maybe this would be a good time to ask about specific issues the participants have regarding databases. I believe we have several marketing people online.
Steve Sanchez: OK let me make a comment or two and then remind me to go back there. This is important.
I promise not to bore you with lots of techno babble but there are just a few things you must know about the databases behind these applications. NOT all accounting applications are made the same. Many off the shelf packages like Quickbook, Quicken, etc will not support this type of integration. Some of the newer versions of Peachtree will but not the older versions. For those of you who use applications like Solomon, MAS90, Greatplains, and Sage you're all set. Those applications have OPEN DATABASES. This means that programmers can get to the data easily. An applicaiton like that can be easily integrated with others.
Steve Sanchez: OK Scott
Linda Parsons: What can you tell us about mail merges from databases?
Steve Sanchez: Mail merges are often a function of the application itself. Which means it will be different for each application, but essentially I gather you are trying to get names and addresses from your database?
Michelle French: We are trying names, address, fax, phone, e-mail, etc…having conversion problems.
Linda Parsons: Yes, that's correct. The database would contain contact information from Microsoft Word, for example.
Steve Sanchez: The information is coming FROM Microsoft Word or going TO an MS Word document?
Linda Parsons: From Excel to a Word document
Steve Sanchez: OK now I understand the problem. Essentially a lot of the difficulty stems from the fact that Excel is not a database application.
Michelle French: You make it sound so simple... we are having lots of problems integrating some of our PACS info into VPM
Steve Sanchez: You are not alone in this.
Cheryl Fairbanks: Steve, we enter client info twice -- in MAS90 and again in Access -- can we somehow only enter it once but get out what we need?
Tiffanye Warren: What is the best way to structure a Contact Management Database? What specific sorting options have been successful with most firms?
Scott Cytron: Steve, are there commonalities between these issues? Say, how the data is actually transported - regardless of the vehicle?
Steve Sanchez: Yes but in this case it is the vehicle that is important.
Dawn: Access will take information from Excel and store it in a database for you. From this, you can reuse the data in Word, put it back in Excel, throw it out to the Scheduler, practically anything you want. I do it all the time at work and everyone is amazed. The trick is learning which programs are ODBC compliant and which aren't and you therefore have to work around.
Steve Sanchez: Dawn is right when she says to use Access. Excel is a spreadsheet program, and not designed to act as a database. Access is a database program. Your option from a database are many when compared to your options with a spreadsheet program.
Dawn: That most people use Excel as a database is the underlying problem with most companies. Steve is right. Get the information out of Excel and into Access - it is so much more flexible!
Kristy Muller: Doesn't Microsoft recommend a limitation of no more than 15 concurrent users in an Access database?
Steve Sanchez: Yes. Access is limited when it comes to concurrent users. It can be optimized for up to 25 users but if you have more than that it becomes unstable
Michelle French: Thanks for the insight, now how about conversions? For example, we have had multiple problems converting our PACS information into our VPM database.
Kristy Muller: Does Act have such a limitation?
Cheryl Fairbanks: Yes, but I think the MAS90 database is limited as to how much information it can take -- am I missing something?
Scott Cytron: Steve, should we learn how to "think" about data first? And then cover the specific issues later?
Steve Sanchez: Sure.
Scott Cytron: I will make a note of the questions and come back to them.
Steve Sanchez: Some of these applications are so easy to use that the temptation is to believe the hype--- so to speak. For example...One particular company is promising to make a database expert out of anyone who buys their product. I would suggest that if your organization is using the same spreadsheet over and over and over for the same task that as a function that should be transferred to a true database.
We recently met with a new client who is using thousands of spreadsheets for the same task. From a service point of view it's a nightmare because when a client calls no one has the information at the ready. They have to take the clients request down, and call them back. In addition since the same information is stored in more than one location the data has a greater tendency to become corrupted. That is, people enter some information into one spreadsheet and it is not updated in say the contact manager. Thee bill goes to the right house, but the company newsletter doesn't. That will make any company look bad.
I think what you want to think about when you think about data is CENTRALITY. You want the data that everyone will use to be in ONE LOCATION. You want it to be accessible to everyone who needs it and you want FLEXIBILITY with the way it is stored.
Linda Parsons: In one location and updating continually as changes are made.
Steve Sanchez: That's right Linda.
If I take a call and update a customer’s financial data, why should you have to make the same call to update YOUR copy of the spreadsheet? That just makes no sense. If the information is in a central location when I make the change YOU BENEFIT FROM IT!
Michelle French: We currently have a paper chain & it does NOT work.
Dawn: One problem we have is that we have two different programs, one for our tax returns and one for our time and charges. We keep client lists in both. Currently, we have two people updating the systems, one does one program and the other handles the other program. Both are ODBC compliant. Can both these be linked into Access so that the information would only have to be entered once?
Steve Sanchez: This is a great example Dawn Thanks. If both databases are ODBC compliant you should be able to integrate them together and skip Access. No middle man so to speak!
Steve Sanchez: From a technical point of view we're talking about a MIDDLEWARE application that would manage the synchronization, etc. But the bottom line is that you can hire a programmer to integrate those databases. This is a good example of efficiencies created by integration. BUT pick your programmer well.
Linda Parsons: We have accomplished the one person doing the updating in one place, but right now that information is not in a database. We need to get it into a database and replicating so multiple people can use the information.
Liz Silveira: Can Act be linked into Access and do you feel that Act is a good contact management database or do you recommend another?
Steve Sanchez: The latest version of ACT is ODBC compliant and so integrating it should not be a problem.
Dawn: Does it really have to be a programmer? I understand Access and have basically become an in-house application developer. Is this something I can do?
Stuart Avera: Some application vendors do not want users writing back to their datasets for integrity reasons. It may void support agreements.
Steve Sanchez: Scott Stuarts question is a good one don't let it get away!
Steve Sanchez: Dawn, I can appreciate that, let me tell you what we run into. A company decides they want a database. They hand it do a very sharp employee who has some experience. The employee develops the application and it works...for a while. The company put all its mission critical data into it. They become dependent on it and everyone uses it all the time. And then the day comes when it stops working. The person who developed it can't figure out why and the company has to call in a consultant and spend a lot of money to figure the problem out.
Now I'm not knocking your skills at all. I would never do that. I'm just telling you my experience. The company meant to save time but in the long run they lost time and money because they can't live without this database, but it doesn't work.. Now I'm a consultant, so you would expect me to say USE A PROGRAMMER, but Its true (in this case:-). Choose someone whose specialty is databases. Choose someone who has integrated applications before. Choose someone who will work WITH YOU not give you a quick fix. There is nothing worse than a consultant who knows everything you need. You want someone who will listen to your needs and work with you to develop the best application for your needs. Check their references. Make sure they are certified. That's why it is critical that you have a great relationship with your programmer, because switching it a bear.
Dawn: I understand. But I've also fought with a lot of programmers who also don't understand the program they designed too. It goes both ways, but I do agree that most people should hire out. Good ideas though. Thank you.
Steve Sanchez: Now back to that question about companies not wanting their customers to do work on their applications for warranty reasons. That is true in some cases but it is changing. If a company’s database is ODBC (Open DataBase Connectivity) compliant they want to allow you to get at the information stored in the database and use it in other applications. What they won't let you do is alter the fundamental database structure of the application.
If the database is OPEN (memorize that question...IS THE DATABASE ODBC COMPLIANT!), then you can add information to the database from another application or from the web.
For the Marketing folks out there, I see a lot of web surveys around these days. These surveys collect all kinds of information. In some cases when you press the submit button the website fires off an e-mail to the company. Someone gets that email and RETYPES THAT INFORMATION INTO A DATABASE! Good night, what a waste of time. That web page can be linked right up to a database and the person taking the survey does the work. If you're worried about whether the data is valid data or not, protections can be written into the program to make sure they give a valid zip code for example. I'm really excited about the possibilities the web offers when a browser is connected to a database.
Steve Sanchez: One of the biggest plusses is GEOGRAPHIC INDEPENDENCE. If the company database is online I can get to it from anywhere I can get a dial tone.
Scott Cytron: So, Steve, could you recap the main points of the workshop today before we get into the questions? It's time we start wrapping up so we don't run over our time together.
Steve Sanchez: First think CENTRAL LOCATION when you think about storing your data. Keep it in one place and have everyone come to the source for their updates
Second, you can integrate any database application that is OPEN, or OBDC compliant
Third, a spreadsheet is not a database! If you use the same spreadsheet more than 500 times you may need to think about a database.
Fourth, choose a good programmer, (or a Great employee!)
Scott Cytron: Does database integration always have to cost a lot?
Steve Sanchez: Deciding what "a lot" is will depend on what the cost savings will be. If your customers don't get their W-2s on time because the address was wrong in the spreadsheet, well that was one very expensive spreadsheet.
Scott Cytron: Okay, let's get to the questions...What about converting data from PACS to a VPM database?
Steve Sanchez: A database can save lots of those kind of costs and help justify the new development. Specifically I can't say...but if the databases are open remember a programmer can get the data out if it even if the company won't let you do it.
Scott Cytron: Next question: How do you redirect information from an accounting package like MAS 90 to ACCESS or some other information manager without having to enter it twice? Does this have to be a function of the software itself?
Steve Sanchez: No. A programmer can write a MIDDLEWARE application that will do it for you. ALL BECAUSE THE DATABASE IS OPEN.
Tiffanye Warren: Regarding keeping the data central - what if some partners do not want to broadcast their client fees on a central database?
Steve Sanchez: Tiffanye, security can be written into the application to protect the private data. The same can be said for the web.
Scott Cytron: Question: What tips can you offer for mail merges?
Steve Sanchez: Use Office 2000...It has a great wizard that will walk you through it. Otherwise use the comma delimited format to import the data into Excel. then clean it up there.
Donna A. Cohen: If you are using VPM, you can do mail merges from there.
Scott Cytron: Next question: What is the best way to structure a contact management database? What specific sorting options work well?
Steve Sanchez: The fewer the categories the better. When you have to many it becomes unmanageable quickly.
Scott Cytron: Steve, this is the next to the last question... Can ACT be linked to ACCESS and do you think it is a good contact management program?
Steve Sanchez: Yes. The latest version of ACT! is ODBC compliant and can be integrated. Older version were not so flexible. I use Outlook at InfoSolve but many people I know swear by ACT! and Goldmine as well.
Scott Cytron: It sounds like database integration is a real issue for most of you. I hope you have learned a few things today in our workshop. Thanks, Steve, for doing a super job! Are there any more questions before we sign off today?
Michelle French: Steve, are you for hire?
Steve Sanchez: You bet! e-mail me at email@example.com
Michelle French: Thanks a lot!
Scott Cytron: Okay, everyone! Thanks for a great session. The transcript will be posted shortly.
Steve Sanchez: Thanks for reading!