How to Create a Database in Access Data Type

Manage data types with an Access database.

The beauty of creating a database in Microsoft Access is that you don't need to be a computer genius to be able to create tables, design forms, develop reports or generate queries. Microsoft Access uses many of the same commands and tools that users are familiar with in Microsoft Word and Excel so if you have some familiarity with these software programs, you should be able to create a simple Access database in a short period of time. However, if you intend to track customers, products and sales, then the project will be more complex and take more time to design.

Things You'll Need

• Data

Instructions

1. Designing the data base requires a determination of how and what data will be captured. Think through how the data will be used, analyzed and shared. For example, a customer list might include the name of the business, a contact name, a contact phone number, a contact title, a street address, a city, a state, a zip code and a country.

   Expecting a technician to enter this information will require you to create a form to allow for inputs. If you have multiple sources of data inputs, you may want to consider a SQL server that will allow several people to input the data simultaneously into the same data base. Standardize data fields (column headers) so that Access will recognize the records in each location and each table. The headers must be consistent in each table or the Access database won't work correctly.

2. Define the data fields in each table. The Access database software works best when information is stored in a singular table and is not repeated in another. The software will automatically create look up tables to help users access their information once it is imported or stored. Define separate data field names for each data group such as "customer_name" "street", "city", "state", "zip_code" "country" and "business_contact".

   A complete record in the table would consist of the data fields: customer name, street, city, state, zip code, country and business contact.

3. Define the data type of each field. Specify what kind of data should be permitted in that field. Alphabetical text fields can be counted but they cannot be summed. The "state" field in a customer list is typically defined as alpha text with 2 digits. Specify formats for dates. Calculations can only be performed on numerical fields so you must specify that text is numeric if you want to perform math functions.

4. Define the unique key field. The Access software requires a unique identifier for each table. A customer number may act as a unique key field in a customer list. A list of products may use a unique part number. If there is no unique field within each table the Access software will create one.

5. Create an excel worksheet and copy and paste the data into excel first, taking care to use the headers or titles of columns you defined earlier. Do not leave blank fields. Then when the data is set, save the excel file. Then open up access and Select "File", Choose "Import" and Browse to where the file is on your computer.

   As the access software reviews your data, it will allow you to modify the names of the fields, or disregard fields and not import them. Next through this pop up and Click "Finish" When done, you will have your customer list table in Access.

Tips & Warnings

• Use an underscore character rather than a space between words as Access won't accept blank spaces in header names.

• Data fields that are left empty may become corrupt; it is best to type in "blank" or "na" in all blank fields.

References

• Access Tutorial