How To Create A Database In Ms Access 2010

Creating New Databases

A new database is a container that will hold all of the tables, form, reports, queries, macros, and modules that you create. In Access 2010, you can create a new database by clicking the "File" tab in the Ribbon. Then click the "new" command. Then select the "Blank database" choice in the "Available Templates" section. In Access 2007, you can create a new blank database by simply clicking the "Blank Database" button In the "New Blank Database" section of the "Getting Started with Microsoft Access" welcome screen. In the "Blank Database" pane that appears at the right side of the screen, you can enter a name for the database into the "File Name:" text box. If you want to change the default folder where the database file will be stored, you can click the small folder button at the right end of the "File Name:" text box in order to launch the "File New Database" dialog box. Use this dialog box to give the new database file a name and also select where you want to save the file. When you are ready, click the "OK" button to close the dialog box. Then click the "Create" button to create the new database file. Once that is done, the new blank database will appear in the main Access interface.

Flow Overview of a Database

A database should be simple, logical, and straightforward in its design. In general, you use forms to enter information into tables. The data is then stored into these tables, which are related to each other as necessary. You can the use queries to pull specific information from the tables in the database. The queries often form the basis for reports, which will then allow you to view the information you requested. Once this system is in place, you can automate it by using macros and modules to simplify and streamline the processes involved in entering, storing and retrieving data. This is the main reason that you use databases: to enter, store, and retrieve data.

The 'Flat-File' Method of Data Storage

Access is a relational database application. So what does the term relational mean, and how is this important? The term relational describes the method used for storing data within the database tables. However, it may be easier to understand the relational model of data storage by contrasting it with another method of storage that you may be more familiar with: the 'flat-file' method.

Information is frequently stored in large 'flat-files.' For example, assume that you want to create a database file that stores your company's customer information. You would begin by listing the different attributes of the customer that you wish to record. You may want to record customer information like the "first name," the "last name," the "company name," and other relevant pieces of information. Perhaps you could create a table in an application like Microsoft Excel where you can create columns for each piece of information that you wish to record. You can then list each customer's information in the rows underneath the columns, creating a basic table. Assume it looks like the following example.

For many types of databases, the structure shown on the previous page would work well. This is a 'flat-file' list or table. What you are doing when using this type of database is recording a single piece of information, like the "FirstName," "LastName," or "Address," about a single entity- in this example, a customer. The reason that this type of data structure works well in the example given is because for each entity (the customer), you are only recording information that has a "1 to 1" relationship to the entity.

So, what is the meaning of this "1 to 1" relationship between the entity (the customer) and the data you are recording ("FirstName," "LastName," etc.)? What this means is that for each entity, or subject, (in this case- the customer), you are only recording information about that entity for which there would only be one "answer." For example, each customer would only have one "first name" and one "last name." They would work for only one "company." So the term "1 to 1" refers to the relationship between the subject of the table (customers) and the data being collected about the entities. Because for each (one) customer, there is only one possible piece of data to record in the column, you say that the relationship between the data and the entity is "1 to 1." If this is the type of database that you are trying to create, simple Microsoft Excel tables will work well.

The problem begins to occur when you try to use a 'flat-file' approach to model a more complex entity or subject, like "sales." For example, assume that you wanted to expand the customer database from the last 'flat-file' database in order to include sales data. Let's say that now, in addition to the information already being collected, you also want to record each order made by a customer.

First, you would start by listing what data about each sale that you want to record. Keeping the example simple, assume that you decide to record the "sale date," the "items" purchased, the "quantity" of items purchased," and the "amount" paid for each item. You may decide to add the following columns to the 'flat-file' data structure.

This may appear to work, at first glance. However, you will immediately begin to encounter problems when you begin to enter records into the file. To begin with, each time a customer makes a purchase, you must re-enter all of the "FirstName," "LastName," etc. information again. This alone is irritating enough.

One solution often proposed at this point is to enter another row (with all of the redundant information) once for each item purchased. However, you will soon find that this file will grow quite quickly down the table, and you will also have to enter in a lot of redundant customer data for each item purchased. This is not an elegant solution and will inevitably waste both the time and effort of the person who performs the data entry.

Another solution often proposed at this point is to create additional columns (like "Item1," "Item2," "Item3," "Quantity1," "Quantity2," Quantity3," etc.) instead of having to enter additional rows of information. While this may seem like a good alternate solution, what will you do when someone purchases 100 items? Will you really create a set of 3 columns ("Item," "Quantity," "Amount") for each item purchased, producing a table over 300 columns across? Would you simply leave them blank if the person orders only 1 item, wasting valuable storage space? In this solution, you are simply substituting columnar growth (across) for vertical growth (down). This is not an elegant solution either.

So why is there a problem now, when there wasn't one earlier? The answer is that now you are no longer trying to model a "1 to 1" data relationship in the file. Recording sales information is simply more complex than recording customer information. What you are trying to record now is what is referred to as a "1 to many" relationship. Basically, for each entity (the customer), you are now trying to record data in the columns which could occur more than once per customer (the "Items" ordered, for example). You would be in a sorry state if each customer could only purchase a single item. You must allow for the fact that in a sale, each customer may order many items. The relationship between customers and items purchased is a "1 to many" relationship. When you find that you are trying to model a "1 to many" relationship, it is then that you must abandon the 'flat-file' method of data storage where you try to place all of the information that you want to record into a single table, and instead turn to the relational model of data storage for the solution.