Analytics mindset

IntegrateCo

Part II:

Before you can perform your analysis, you must load the data into your data analytics tool. For this case, you can assume that Noah competently extracted all of the information from the two systems and that the pipe-delimited text file you were given is complete and accurate. Your responsibility is to load the data into Microsoft Access. Then, you will transform the data so that you can compare 2015 and 2016. 

While most students and business professionals are more comfortable using Excel than Access, there are three reasons why Access may be superior for this case. First, it will be easier to automate the analysis for reporting purposes if Access is used. By automating the analysis, Noah can run the analysis reports quickly and easily each month for the rest of 2016. Second, using Access makes it much easier for an internal or external auditor to review and re-perform the analysis. Third, Access can handle much greater volumes of data than Excel. If the company had more employees (e.g., from another division), then Excel may not be able to easily handle the additional data if it were a large data set.  

Required

► Load the data into Access. Below, are a few suggestions to keep in mind.   

– You should import the data into three different tables. One table should hold the 2015 data, another table should contain the 2016 data and the third table should include the job codes. Label these three tables as tbl_2015Data, tbl_2016Data and tbl_JobCodes, respectively.

– Follow Access leading practices, including:

► As specified above, name the tables using the prefix tbl_ so it is easy to keep track of different item types in the database.  

► Field names should not contain spaces. So, instead of using Customer Name as the field name, it should be CustomerName or Customer_Name.

► Special characters (e.g., $, &, %, ; or ,) should not be used in field names.

► Field names should be descriptive and provide some information about what the field includes. Abbreviations are acceptable to avoid long field names.

► Capitalization in the field names can enhance readability. Instead of acctnumber, the title AcctNumber is easier to understand.  

► Once the data is loaded into Access, make the 2015 and 2016 data “talk together.” That is, be aware that the different years of data are aggregated at different levels and you have to extract job code information to be able to compare the data between years. Consider the following:

– Write one query that extracts the job code for 2015 and a second query that extracts the job code for 2016. In each query, include all of the other fields from the data import for each year; add a new field that has the extracted job code; and, for the 2015 data, add the employee’s full name in the same format as it is listed in 2016. 

– Label the queries qry_2015WithCleanJobCode and qry_2016WithCleanJobCode for 2015 and 2016 data, respectively.  

– While there are multiple ways to extract the job code, one way to do it is using IIf, Mid and Like functions in a complex formula. As an additional hint, “Like "[A-Z]*"” will examine a character to see if it is a letter. Combining this function with an IIf statement can help you determine if different characters are letters. 

► When you finish loading the data, upload a copy of your Access file for grading. Make sure you have written and saved the two queries suggested above to extract the job code information.

Data