Background

You serve as a data analyst for IntegrateCo, a company that installs and services integrated building management systems in the Intermountain West of the United States of America. Building management systems are computer$-$based systems installed in buildings to control all mechanical and electric equipment in the building. Examples of mechanical and electric equipment controlled by the building management system include lightning, air conditioning and heating, security systems, power systems, sound systems, video cameras, door$-$locking systems, etc. IntegrateCo serves both large and small customers. It is privately held and expects to have $$15$ million in revenue in $2016.$

The CFO, Noah Summers, noticed that payroll for the first six months of $2016$ was approximately $$266,000$ more than in the first sixth months of $2015.$ Noah wants to know more why there was a difference and asked you to compare payroll for the first sixth months of $2016$ with the first sixth months of $2015$ and report to him what you find.

One complication in performing this analysis is that IntegrateCo implemented a new accounting system at the start of $2016.$ Thus, the payroll data in $2015$ and $2016$ came from different systems. As a result, you will have to take great care to understand the data output from each system and appropriately combine the data for your analysis.

Upon receiving this assignment from Noah, you recognize that you will need to exercise an analytics mindset to respond to his request. As a reminder, an analytics mindset.

Solve or give instruction how to

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$\_2015$Data, tbl$\_2016$Data 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 acct number, 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$\_2015$WithCleanJobCode and qry$\_2016$WithCleanJobCode 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.