How To Create A Database For A Small Business

There are lots of situations where you will want to create a database to store business data. It might be you need to record your assets or perhaps a list of customers or contacts. At first glance Access might not seem like the go-to tool of choice; it looks more complicated than it is to use. I'll show you how to create both your own Access database plus a report and query to go along with it.

To get started with your first Access database, launch Access and choose File > New and then click the Blank Database option. Type a name for your database and click Create. On the screen you'll see a dialog containing links to the type of objects that might be included in your application. You won't use all of these but you will use Tables, Queries, Forms and Reports in most cases.

Create a Table
You start by creating a table. Click the Tables link in the Objects list. Choose Create Table by Using Wizard. The Wizard includes sample table structures that you can use to build your own table. For example, click the Contactsentry to see a list of fields often used in Contacts tables. Add the fields from the list that you want to use in your contacts list. If you don't need a particular field, don't add it. You will probably want at least first name, last name, address, city, state or province, zip code and phone number field.

Select each field so it appears in the Fields in My New Table pane. To rename a field, select it and click Rename field. For example, you may choose to change EmailName to read EmailAddress. Click Next to continue. Type a name for your table and select the 'Yes, set a primary key for me' option and click Next. In the following screen select the 'Enter data into the table using a form the wizard creates for me' option to have the Wizard create a data entry form for you, and click Finish.

Enter Your Data
The data form that the Wizard creates will make it easier for you to enter data into your table. If you chose to create a primary key field, you will find an additional field in the list that has the auto-number entry already in it. Access will automatically give each record a unique sequential number as you create each record. Enter the data for your contact and press the Tab key to move to the next field. When you have completed one record, click the button with an asterisk (*) on it at the foot of the form to add a new blank record. Continue and add details of all the contacts in your list.

Click the Close button when you are done. Answer Yes when prompted to save changes to the design of Form1, type a name for the form and click OK. Your table and form now appear in the Tables and Forms areas in the Objects list. To add new contacts at any time, double click the Form name and add more contacts.

Create a Query
It's one thing to put data into a database and quite another thing to get it out. To access your data you will need to create queries to extract the data for you. To create a query click the Queries option in the Objects list and choose Create Query by Using Wizard. From the Tables/Queries dropdown list select the table that contains the data that you want to search and view. From the Available Fields List add the fields that you want to appear in the query result.

For example, if you want to know the first and last name of contacts that live in a particular city you should add the first name, last name and city fields to your query. Click Next and type a title for your query that explains its purpose. Click the Open the Query to View Information button and click Finish.

You will now see the Query results on the screen showing all the details that you selected to view. However, right now, all the records from your data file appear in the query results. To filter the list so that only selected records appear click the View button at the far left of the toolbar and select Design View. This takes you to design view so that you can customize the query to retrieve a subset of the data in your database.

For example, if you have retrieved the first name, last name and state or province for all records in your file, you can limit this to just one state or province. To do this, locate the StateOrProvince field in the list and click in the Criteria row for this column. Type the following text into the Criteria cell:

[Type state abbreviation]

Next, click the Run button (it has a red exclamation mark on it), and you'll be asked to type a state abbreviation. Do this and press Enter, and you'll see only records for contacts in the state you've typed.

Query screen shot
You can limit a query to a subset of your data. This one, for example, prompts you for a value by which to search.
(Click for larger image).

Create a Report
Another method to get data out of your database is using a Report. From the Objects list select Reports and then select Create by Using Wizard. As you did for the query, select the table that contains the data for the report and select the fields that you want to appear in the report. It's not necessary to include all fields, just select those you need in this report.

Once you have selected the fields, click Next and apply a grouping to the report if desired. You could use this, for example, to list people by the various countries or regions in which they live. When the report is printed, you can look at a particular region and see everyone in your contacts list that lives there.

Report screen shot
When you create a Report, you can select a field to group the data, which makes it easier to read and use.
(Click for larger image).

To select a grouping, click the fields in the Fields To Group By pane and click the chevron button to group by this field. Click Next and select the ordering for the fields. For example, you may choose to list your customers in order by last name and then by first name.

Click Next when you are done, and then click the layout you want to use for the report. Click Next and choose the layout you want (for example, align left) Next you can choose how you want your report to print on paper — either landscape or portrait orientation. Click Next, choose a style for the report, click Next and then give your report a name. Click the Preview the Report button and click Finish. The report will be created and displayed on the screen for you.

When you are finished, close the report. The report will be saved in the Report section of the main dialog, and you can double-click its name to access it again at any time in the future.

Helen Bradley is a respected international journalist writing regularly for small business and computer publications in the USA, Canada, South Africa, UK and Australia. You can learn more about her at her Web site, HelenBradley.com

Do you have a comment or question about this article or other small business topics in general? Speak out in the SmallBusinessComputing.com Forums. Join the discussion today!