Community Pick: Many members of our community have endorsed this article.

How to create an Access database for a business overview


I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were using spreadsheets. It seems they bought the software thinking it would help but found out it did not work like their business did, and was not flexible enough to accommodate them.


When asked if I could suggest new software that might help, I asked them to show me how they ran their business. I collected a copy of each piece of paper that they used.


On the floor of my office I laid out all the forms and reports in the way that their business flowed. Next I took an 8 1/2 by 11 engineers blue grid paper pad and started drawing and labeling boxes where data would be input. Each data collection form would be on its own page.


At this point I would take the paper forms back to the client and run through them as if they were looking at a computer screen. Going through this process helped me become better acquainted with the flow and relationships of the data.


Next using colored sticky dots, I would put dots on each box that seemed related. This gave the basic design for the tables. I started MS Access and I created all the tables. Then I createe queries for the forms and then designed the forms.


For the first several weeks, the tables were pretty straight forward but the queries were a little more intense. The forms and reports were the hardest because this is where you have to make things flow the way a human would do it.


Things are more complex now (I started in 1998 before the internet). Now there are thousands of sites with free to paid support. What used to take me a few weeks to figure out, I can now find a solution for in a day or two on the Internet. When I find a very good site, like Experts Exchange, I will usually sign up and pay the fee for support; the small amount they ask for in a year will most likely pay off in my first request for support.

The reason I wrote this is to encourage you to create something that works the way you do. Trying to accommodate yourself to the way some generic software wants you to perform will stymie you for the rest of your business life. The other reason to do this is, very few businesses stay the same day in and day out. When you create your own database you can change it as your business changes.

If you really do not feel you can do this, try to find someone in your local community to write the program for you or to assist you in getting started.

 If you are a young person looking for an adventurous life, walk into businesses in your local community and ask how their software is working for them. I even wrote a few early databases for free to cut my teeth so to speak and for the experience (one of them wound up being my largest client). Once word gets out that you can do the above for businesses in your community you will have a great career and life.  



Comments (3)

I did exactly the same thing for several clients. It's the only way to get an end result that meets their needs. However beware of writing a program that is too specific to current needs. Make as much generic as possible so the program can grow and also so it can more easily be adapted to new clients with similar needs.
I have done much the same thing for clients in the engineering field.  Almost always they are running their business from spreadsheets.  The great advantage of spreadsheets is their ease of data input and flexibility.  I write Access databases that read the data from the spreadsheets and then analyse and report the data from Access.  The client can keep operating  while you are installing and debugging the system and the massive effort in writing user friendly data input forms is avoided.  Remote sites can email in their workbook daily.  By loading all the data from the workbook into Access, any backdated changes that the site has made will automatically be included.
The other interesting outcome I have noticed is that as my experience increased I made the system simpler, and as the system became simpler it also became more flexible.  The challenge is to find a simple way to code complex user requirements.

Great article, I am currently working for a client building their database in MS Access.  Hoping to find more customers in the next few months.

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.