Design and Plan a Database
Before creating a database, it is very important to plan it carefully. You need to know what information the database will store and analyse how the information stored will be used.
There are four steps in producing and using a database:
- Define the tables in which to store the data.
- Add data to the database - usually using a form.
- Extract information from the database - using a filter or query.
- Report - present the information in the database effectively.
At each stage of database design, when designing tables, forms, queries and reports, you should ask yourself the following questions:
- How can I ensure that data is entered quickly and without errors?
- How can I ensure that information is quick and easy to find?
Designing Database Tables
In a simple database, all data is stored in one table. A table consists of fields and records, stored in columns and rows. Each table can be defined with up to 255 fields.
Many Access databases use more than one table to store data. For example, in a database of customer orders, details about the customers, products, and orders could all be stored in different tables. Storing data in several tables makes the database more flexible and easier to maintain.
This type of database is called a relational database, because relationships are created between certain fields in the different tables in order to tie the database together.
Here are a few basic hints and tips about designing database tables.
- Work out the design carefully and test it before starting to use the database. Think about what information you need to store in order to identify what fields to use for records. Study any existing examples, or similar databases, and get feedback from the people who will use the database.
- Choose a name for each field. Use names that make it obvious what data the field is storing without being too lengthy.
Some useful conventions to follow when naming fields are to capitalise words and use an underscore or hyphen instead of spaces between words. For example, a field containing a customer\'s address might be Customer_Address or CustomerAddress.
- Choose the data type for each field - see below.
- Test the design with some \"worst case\" examples.
- Decide if fields are missing or if any are not required.
Adding Data to the Database
Once the database structure is in place, you need to enter your data. This is often referred to as populating the database. There are basically two ways of doing this:
- Manually enter data by typing it into a form or datasheet.
- Import existing data from another file.
Data entry is made more reliable and efficient by designing data entry forms.
Extracting Information from the Database
Once a database has been populated with some data, you will want to put it to work supplying you with information.
Data is extracted using filters and queries. A filter works in a similar way to the Find tool. It is basically a question asked of the data, such as \"How many records with the word \"Germany\" in the Country field are there?\" However, instead of selecting the records that match the criteria, a filter hides any records that do not match. You can apply a filter to any view of data, including datasheets and forms.
A query is a sophisticated tool for selecting and displaying records in different ways. In a query, you can specify which fields to display, the sort order of records, and multiple conditions for selecting a record. You can also use queries to perform calculations and summaries.
Reporting Data
When you have extracted information from the database, you will often want to send it to someone. You can quite easily print what you can see on-screen, but for a professional-looking record you will need to create a report.
A report is used for data output in much the same way a form is used for data input. You can select what information to show (often using a query to select certain records), perform calculations on the data, and present the data attractively by making good use of fonts and graphics.
