Designing a Database
To design a database, you need to first create a database table and then define the names of all the fields you want to store in that particular table. Database tables let you divide a file into separate parts. For example, one database table may hold the names and addresses of all your customers, a second database table may hold the names and addresses of all your employees, and a third database table may hold the names and addresses of your suppliers. Access stores all this related information in a single Access file that is saved on your hard disk.
| Customer Table: | Employee Table: | Supplier Table: |
| Name Field | Name Field | Name Field |
| Address Field | Employee ID Field | Invoice Number Field |
| Phone Number Field | Salary Field | Amount Due Field |
To design your database, you can create a database from scratch or use an existing template, which you can modify. Designing a database means defining both the number of fields to use for storing information and the maximum amount of data each field can hold.
If you have a field that stores last names, what is the maximum number of characters you want the field to hold? If you make the field hold too few characters, people with long last names (maybe they are hyphenated) won’t be able to enter their entire name. Conversely, if you make the field hold too many characters, you waste storage space.
Similarly, if you have a field that stores numbers, what are the maximum and minimum limits on the numbers you want to save in that field? If you are storing someone’s age, you probably do not want the field to contain negative numbers or numbers beyond 200. If your field needs to hold salaries, the field may need to hold large numbers but no negative numbers.
In general, store information in separate fields. So rather than create a single field to hold someone’s full name, create two separate fields: One field holds a first name, and the second field holds the last name. By storing last names in a separate field, you can easily yank last names out of your database to create form letters that state, "The John family has just won $15,000 dollars in the Publisher’s Sales Pitch Sweepstakes!"
Creating a Database from Scratch
Access can create a blank database or a database using one of many templates available from the Microsoft Web site. No matter how you create a database, you will likely need to modify it to customize it for the type of data you want to store. To create a blank database, follow these steps:
- Click the Office Button and choose New. The Getting Started with Microsoft Access screen appears.
- Click the Blank Database icon. The File Name text box appears in the right corner of the screen.
- Type a descriptive filename for your database. You may want to click the Browse icon to specify a different folder or drive to store your database.
- Click Create. Access displays a blank table and an Add New Field text box. Displaying your database in rows and columns is Datasheet view.
When you create a database, Access automatically creates a special ID field to help you sort and organize your data. (You do not have to use this ID field if you do not want to.)
Creating a Database from a Template
Just like it is easier to copy someone else’s work, it is easier to use an existing database template and modify it for your own needs.
You need an Internet connection to retrieve any database templates from the Microsoft Web site. To create a database file from a template, follow these steps:
- Click the Office Button and choose New. The Getting Started with Microsoft Access screen appears. and then click a category under the From Microsoft Office Online category, such as Business or Sample. A list of available templates appears.
- Click a template. Access displays a default name in the File Name text box in the bottom right corner of the screen.
- Type a descriptive name for your database in the File Name text box and then click Create. Access displays your database in rows and columns (Datasheet view).
