Overview, How to Make an Access database

I recommend that--except for the simplest of databases--you split the database, something few people do. That is, create one database and name that file, say, "db1_tables." Your tables will reside there, everything else will reside in another database named, say, "db1_front." In the 2nd database where everything BUT the tables are, you will then "link" to the tables in "db1_tables." You will do this by selecting "File....Get External Data...Link Tables" from within the "db1_front" database after you've made the tables in the "db1_tables" database. The reason I recommend splitting the database like this is because you can continue to "fix-up" the cosmetic & functional aspects of your database a LOT easier after you've given it to the person who will be using it (assuming it's not you) and do so without fears of overriding their data. Plus, that design lends itself a LOT better to networked computers. However, if you are positive the database will be used on one computer by one person only, then having 1 database with all the tables, queries, forms, etc should be fine. Also, if you design it this way but later wish for it to be split, it can be.

Step 1: Good Table Design

Don't Put All the Fields in 1 Table. Divide Them Up Into Related Tables and Link Them

The first step is to make sure your tables are designed well. Mainly, make sure you have the proper fields that you need, and that the layout is the way it should be.

One mistake that very entry "designers" tend to make is to try & place everything in 1 table. With Access, it's commonly much better to have the data parced into 2 or more different tables. Let me give 1 very good example:

You have a database showing sales. It is to hold customers on file to whom you sell products to regularly. It should also hold a record for every sale by that customer (or any others.) One common mistake for a beginner to make is to have 1 table hold everything. This becomes very wasteful because that means everytime XYZ Company buys from you, there will be entries not only for each sale but also holding XYZ's address, phone number, name of contact, etc., and those entries will repeat over & over again unnecessarily everytime XYZ purchases something from you. The table would end up looking something like this:

What makes MUCH more sense is to have 1 table designed for each customer, and another table for each sale. The CUSTOMER table would hold every customer you have, along with relevant information: name, address, contact name, phone, etc. Then, in a separate table, you would have an entry for each sale, and instead of XYZ Company and all the details relating to it repeating itself in the sales table, there would be 1 field which would identify the XYZ Company, for example, as the company which purchased from you to create the sale. This way, the details of name, address, contact name, phone, etc. don't have to be re-entered over & over. This saves you not only a lot of time but makes the database more efficient.

Tables designed along those lines would look something like this:

There, you would have a table holding each repeat customer, with all the details. (You would probably also have fax numbers, email address, web pages, etc. listed there as well.) Notice that NO sales are in this table, and each customer is shown ONCE. Notice, too, each line has a unique ID number

Then, you would have a separate table showing each sale. And there would be a field linking to the ID field shown in the above table. You could call it "Cust id" or something like that in the new table. Here's an example:

See the difference? For each item purchased by "XYZ" (and none from "123 were shown, though they would be in here, too), there is 1 line for each item but notice that the details pertaining to XYZ itself--eg., name, address, etc--don't repeat. Instead, the method whereby each sales record related to XYZ is tagged as such is by the "1" field in the "customer id" heading. Notice that this matches the "1" shown in the "ID" heading of the "customer" table. The same logic would apply for any products sold to "123 Company." The entry would have "2" in the "cust id" heading.

Make Each Field the Proper "Data Type"

This is very basic, but true. Each field has different "types" which can be assigned to them. Among the types are numeric, text, date/time, autonumber, and many more. And there are types within types. The point here is to not mismatch types, which can lead to sloppiness and even to errors later on.

You may think, for example, that a field containing someone's phone number would be a numeric field. Not so. The only fields which would be numeric would be fields which would have calculations performed on them (and even some of those wouldn't be numeric). Phone numbers don't tend to have calculations performed on them. The same could be said for social security numbers, or serial numbers, or (to some extent) dates. You would therefore make those fields TEXT fields. By contrast, a field showing the number of units sold to a customer WOULD be a numeric field.

Then, within the TEXT field parameter, Access will assume that your text field will not exceed 50 characters in length. This can be shortened or lengthened. If the field, say, is going to hold the state of an address and hold it in abbreviated format (eg., AZ not Arizona), then obviously you could shorten the field length to 2--or maybe to 5 to account for overseas locations. Or, if the field is going to hold a serial number, you may find a length of 25 to be long enough. In either of these cases, it would be desireable to shorten the length to account for this. However, if there is a good chance that 50 is not long enough to handle all entries, it can be lengthened to a number as high as 255 if necessary. HOWEVER, DO NOT make each field 255 just to "be safe." This wastes space within the database.