Mark E. Buckley

Managing Your Customers with Access

You may own 'Access' if you have Office Professional on your computer. Many business owners have this powerful product on their computers. Most business owners have no idea what this product is capable of.

Access is a database program. You can create different access files for different needs. I have at least eight different databases on my computer.

Access, or other databases, can be used to store large amounts of information. However the power comes from its ability to sort through information to provide the information you want.

I have built databases to manage inventory, create invoices, create estimates, mangage construction projects, manage sales promotions, track revenues and expenses, and to store technical information.

As a business person, one of the key types of information you need to manage is customer information. Perhaps you have some information in paper files, some in emails you have received, some in your computer's address book, some in a physical address book, and other information in your accounting software. With Access you can combine all this information into one integrated file. This data can then be used to do a mass mailing to all your clients. This data can be used to analyze your sales. This data can be used to forecast future customer sales.

To be an expert at Access you would need to understand SQL - Structured Query Language, Visual Basic, VBA, and Database structural techniques. But you do not need to be an expert clock maker to read the time. Your objective is managing your business.

First think through the information you would like to keep about your customers. Make this a very detailed list. You will probably include first name, last name, middle name, business phone number, home phone number, fax number, email address, street address, town, state, zip, and other basic contact information. You might include other marketing information such as how they contacted you, who referred them, when they became a customer, their businesses SIC code, any customers they are related to, etc.

From there you can start to create a database. Click on Access and select File > New. Select Blank Database. Name the file customermanagement.mdb.

You will start on the table section. Click on the 'New' icon. Select Design View.

Now you will see an unfamiliar looking page requesting your desired fields.

In the first row type ID for field name and AutoNumber for Data Type.

In the second row type First for field name and Text for Data Type.

Then click on the View icon just above field name. It will ask if you want a primary key. You answer yes. Then it will ask you to name your table. Call it 'Customers'.

Now you will see a less scary looking screen. It looks much like a spread sheet.

Click back on the View Icon to get back to the scary page.

Now add in all the other types of information that you wanted to collect about your customers.

Use text for the data type until you are comfortable using the others.

Go back to the view screen. Now you will see a large table with all your information ready to be typed in.

The great thing is that you do not have to type it in. If the information is stored on your computer there is often a way to import the information from that other program. This might involve creating CSV or perhaps XML files.

Once you have a reasonable amount of information, you can start trying some of the other features.

You can transfer the information to Excel or Word. You can create queries such as 'show me all customers in zip code 01905. You can create reports such as a list of all customers who have been with you for over 5 years in alphabetical order.

As you build more tables and start linking them you can do some incredible stuff. For one client I can produce a report that would show for a specific client, what products they have bought, when they bought them, at what price, the whole sale price of the product, the sku of the product, the customer's email and spouse's name. It is pretty cool