Heard about the Developer Shed December Giveaway? Peter has a method useful for all things statistical. In this article, Peter shows us how you can make use of some quick methods to generate statistics for your sites or projects in as little time as possible. Using MS Access and a little SQL code, Peter is able to devise an algorithm useful for determining this month's winner.
Lies, Damn Lies, Statistics, and SQL - Into the Database (Page 4 of 6 )
We are now ready to convert our text file into a database table. Access is not an industrial-strength database, like Oracle or DB2, and for this reason it is sometimes maligned. However that may be, for the job we are about to do, I wouldn’t want any other database.
Open Access and create a new database. I’ve called mine “objectivewinner.mdb”, but feel free to choose whatever name you like. If you already know how to import text files into a database you can skip this section and move on to the section entitled “More Data”. For the others, here’s how it’s done.
Using the “File” menu option choose “Get External data“ and “Import”. Make sure you choose the “Text Files” file type and click on “hits.txt”. The “Import Text Wizard” opens and makes your job very easy. Choose the defaults until Access asks for a primary key. Choose “No primary Key”. Continue selecting the defaults through to the end.
When Access has finished importing it will report that there were errors. For our purposes, these are not important, so just ignore them. You should now have a table in your database called “hits”. We’re going to clean it up in a minute but let’s import some more data first.
To ensure the objectivity of our exercise let’s randomly choose two other categories to import, namely the “Java” articles and the “MySQL” articles. (Be assured that the fact that the author of this article has published in both these categories has no bearing whatsoever.) Repeat the steps above to import these categories but make one exception; choose to import your data into the existing table “hits”.
Clean up the Database
Open the table “hits” in design view and delete “Field1” and “Field2”. These fields are not used, so nothing will be lost. Rename the remaining fields “Title”, Author” and “Hits” in that order. We’re now ready to query the database and very soon we’ll have the name of December’s winner.