Database Project

Today, I completed a database project where I had to convert a raw text file into a database table and then perform a few different operations with the imported data. The first thing I learned how to do was the import step. This was achieved simply by just clicking on the home tab, selecting external data, and then clicking on the text file. Since the data was stored in a comma separated file (csv), I selected delimited and continued on with the wizard. The wizard does a great job at making it very intuitive, and even if you are unsure of a few things, there is a help button that will answer all of your questions. I then proceeded to query the data to only show the pieces of data that we are interested in, and this type of query is known as a select statement. Once again, we just click create on the tab, and then select query wizard. It too is very intuitive, so just follow along with the wizard to pick what data you want to see. If further additions need to be made, you can always click the query design button, and here you can add/delete/sort/filter on any number of fields. The query is a great tool to analyze the data, but what if you want to print it out? This is where the report comes in hand because it can take the existing query that you set up, and then provide a printable version of that data. Just click the query you created, and select Report from the create menu. Now the report has been created, you can still add grouping options, delete fields you do not want, or just change the entire look of the page layout. Most of these options are accessible by clicking the view option and selecting design. Now we have a way to look at and report existing data, but what about if we want to enter in new data? There are two ways of doing this: manually entering it into the database table or creating a form. The form is the best way to do it since you only have to select the few fields necessary that need to be inputted. Calculated fields such as create time/auto generated ID’s do not need to be entered or sometimes seen so the form only presents what is necessary to create a new record. As with all of the other operations, just click create and click form wizard. In general, I learned how to take a basic text file and do some basic analysis on it by importing it into Access. I see this being very prevalent in the business world when two companies need to share data.

There are a couple ways that this database could have been improved on, but I am just going to talk about one today. There are a couple fields that have only 2 or 3 different options such as gender and membership type. The problem with the existing database is that it currently allows anything into those fields. For instance, I could put in “alien” where it says gender, and the database would allow that. What needs to happen is we need to put in constraints for these data types and this is accomplished by using the validation rule that is in the table design view. I am going to quickly go over how to put in a constraint on gender to only allow male or female.

First of all: Select the table that needs the constraint and select design view. You should see this:

 

Select the field that needs to be change and select the ellipsis next to where it says validation rule. You should now see this: 

This is where we type in our expression. If you are unsure how to use this click the help button, and it will go over all the different functions and ways to use this. For our analysis, type in: In ('female', 'male'). Hit ok and now save the table. Now when you go to the form and try to enter in a value other than that, it will provide an error saying that the constraint has not been met. This type of constraint should be used to ensure accuracy and better reporting capability.

**Pictures are user provided content by Richard Ecker, 2011. They were taken by using screenshots of   Microsoft Access.